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.