Why doesn't EF return any results when comparing a null variable?
I have a problem with selecting data in a data context in Entity Framework and I have narrowed it down to requesting 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 am I getting data in the second but not the first?
I assume it generates and uses a form SQL query that expects a non-null value:
where x.SomeProperty = @param
Instead of SQL to show C # null-equality semantics:
where x.SomeProperty is null
(the key point here is that in C #, null is null; in ANSI-SQL, null is not null, or (confusingly) is not null - the different syntax is a null check)
I've seen LINQ-to-SQL do the same and agree that it's counter intuitive. The only thing I have is this: check the candidate parameter for null yourself and execute the constant / literal instead == null
. It can probably also do the same by inspecting and expressing and rewriting it if you are in expression trees, but the special wrapper null is easier.
someInt is not null. someInt.Value will be null.