Software Development

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.

2 thoughts on “Checking for NULL using Entity Framework

  1. Or this :

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

    1. That would also work. However, EF produces crazy enough queries as it is so I’d rather code to simplify what EF generates so if I ever have to look it up in SQL Profiler I have an easier time debugging things.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s