Why does EF not return any results when comparing null variable?

Question

I'm having an issue selecting data in my data context in Entity Framework and I've narrowed it down to querying for null values. I have a method like this:

public void DoStuff(int? someInt)
{
    var someData = dataContext.MyEntities.Where(x => x.SomeProperty == someInt);
    // someData always yields no results if someInt is null, even though
    // there are rows in the table that have null for that column.
}

The above method fails if someInt is null. But this line works:

var someData = dataContext.MyEntities.Where(x => x.SomeProperty == null);

Why do I get data in the second one but not the first one?

Answer 1

I guess, then, that it is generating and using a SQL query of the form that expects a non-null value:

where x.SomeProperty = @param

Instead of the SQL to show the c# null-equality semantic:

where x.SomeProperty is null

(the key point here being that in c#, null equals null; in ANSI-SQL, null neither equals null nor (confusingly) not-equals null - different syntax is need to test nulls)

I've seen LINQ-to-SQL do the same thing, and agree that it is counter-intuitive. The only suggestion I have is: test the candidate parameter for null yourself and do the constant/literal == null test instead. It would also probably be able to do the same by inspecting and expression tree and re-writing it, if you are into expression trees - but special-casing the null is simpler.