Checking for NULL using Entity Framework

Here is a curious gotcha using the Entity Framework: If you are filtering on a value that may be null then you may not be getting back the results you expect.

For example, if you do something like this:

var result = context.GarmentsTryOns
    .Where(gto => gto.WeddingId == weddingId
                  && gto.PersonId == personId);

And personId is null then you won’t get any results back. This is because under the hood the query is structured like this:

…WHERE WeddingId = @p0 AND PersonId = @p1

That’s all great when @p1 has a value, but when it is null SQL Sever says nothing matches. In SQL Server, NULL is not a value, it is the absence of a value, it does not equal to anything (including itself) e.g. Try this:

SELECT CASE WHEN NULL = NULL THEN 1 ELSE 0 END

That returns 0!

Anyway, if you want to test NULL-ability, you need the IS operator, e.g.

SELECT CASE WHEN NULL IS NULL THEN 1 ELSE 0 END

That returns 1, which is what you’d expect.

Now, for whatever reason, EF is not clever enough to realise that in the above example, personId is (perfectly validly) null in some cases and switch from using = to IS as needed. So, what we need is a little jiggery-pokery to get this to work. EF can tell if you hard code the null, so you can do this in advance to set things up:

Expression<Func<GarmentTryOns, bool>> personExpression;
if (personId == null)
    personExpression = gto => gto.PersonId == null;
else
    personExpression = gto => gto.PersonId == personId;

This can then be injected as a Where filter onto the query and it EF will interpret it correctly. Like this:

var result = context.GarmentTryOns
                      .Where(gto => gto.WeddingId == weddingId)
                      .Where(personExpression);

The SQL that EF produces now correctly uses PersonId IS NULL when appropriate.