Checking for possibly null values in LINQ

Written by:

I recently encountered some confusing code that was written to work around this issue. Let’s say you want to find all items whose title is null. Using LINQ, you could do something like this:

var titleless = items.Where(x => x.Title == null);

This works just fine in LINQ to Objects, LINQ to SQL, and LINQ to Entities. But what if you instead want to find all items whose title is equal to a variable that may or may not be null?

string title = null;
var titleless = items.Where(x => x.Title == title);

This works in LINQ to Objects, but not LINQ to SQL or LINQ to Entities, due to the fact that it generates SQL something like this:

select * from Items where Title = @x 

which translates into

select * from Items where Title = null 

which doesn’t match anything, because null does not equal null in SQL. It needs to generate this:

select * from Items where Title is null 

You can make it work in LINQ to SQL if you use object.Equals:

var titleless = items.Where(x => object.Equals(x.Title, title)); 

That generates the is null when title is null. But it doesn’t work in LINQ to Entities. You can make it work in LINQ to Entities with this statement:

var titleless = items.Where(  x => title == null ? x.Title == null : x.Title == title); 

But that generates some scary SQL akin to this:

select * from Items where (