Entity Framework Defining Query + IsNull + Left Join = Way Too Slow

I have this SQL statement

select st.Column1, isnull(sot.SomeColumn, 0) as SomeColumn 
from SomeTable st 
left join SomeOtherTable sot 

inside a <DefiningQuery> tag inside my Entity Framework (4.0) .edmx file and I'm running into a serious performance issue. Running the SQL as it's written is fast, but EF wraps the SQL during runtime to inject parameters, which slows it down tremendously.

I can take away the isnull, and it's just as fast in the wrapped EF SQL as it is natively, but I need the isnull to ensure SomeColumn has a value.

Are there any alternatives to isn开发者_开发知识库ull that I could use here that would play nicely with EF?

Thanks for your help.

In the property's properties in the EF designer there should be a default value property, I'm not sure if that will fit your needs though. As an alternative, all of the entities generated out of you EF model are partials. I would add another property that wraps the SomeColumn property (without the isnull) to provide a default value. Given that SomeColumn will likely be mapped as a nullable int, you can define the new property like this:

public SomeColumnWithDefaultValue {
    get { return this.SomeColumn ?? 0; }
    set { this.SomeColumn = value; }
}

You would write your queries against SomeColumn and then use SomeColumnWithDefaultValue where you needed to ensure that it was not null.

My solution was to remove any use of isnull altogether from my SQL and instead include a SELECT NEWID() AS ID (needed this to be SQL 2000+ compatible) with all of these queries. Once I set this ID as my single <EntityKey>, I could allow set any of the left join properties as isnullable=true and I no longer had to deal with the EF slowdown.

Hope this helps someone.