Handling of Nulls in Entity Framework
I think most of you can agree that we are advocates of Entity Framework here. But sometimes things can get a little hairy (as with all ORM solutions). So I just thought I’d post a quick solution to a problem that had me tearing my hair out at work to solve the other day.
Given the following two queries using Entity Framework one would assume the generated sql would the same?
string country = null; var query = context.Orders.Where(o => o.Customer.Address.Country == country); var query2 = context.Orders.Where(o => o.Customer.Address.Country == null);
Wrong. Thanks to this bug and this Microsoft article, I found out that not only will Entity Framwork will not honour what I have specified for ANSI NULLs, it will result in two inconsistent sql queries as the first query will do a “@country = null” comparison while the second query will do a “Country IS NULL” comparison.
Now, while I get that just because two objects are null it doesn’t mean they are equal, the workaround here can result in some ugly looking linq queries! Thank you ever so much Microsoft :)
This is a gotcha for sure. I believe that you can do this:
var query = context.Orders.Where(o=>object.Equals(o.Customer.Address.Country, country));
and this will work when country is both null and has a value.
I still get the same sql output for both .equals and == when you using a string variable set to null Lee. What does sql profiler say for you?