Database null semantics

Doing comparisons with NULL can yield unexpected results.

Problem

Relational database engines treat the NULL case differently. NULL is not a value; rather, it is the absence of a value, so the syntax around it is special. To check if a column value is NULL, this is the syntax we use:

SELECT * FROM MyTable WHERE MyCol IS NULL

Entity Framework, as with other Object-Relational Mappers, has to take this into account. So, what happens if we issue a LINQ query that needs to be executed with a parameter value that may be null? Let's consider this query:

var name = GetParameterValue();
var records = ctx
  .MyEntities
  .Where(x => x.Name == name)
  .ToList();

By default, it will produce the following SQL:

SELECT [x].[Id] AS Id, [x].[Name] AS Name
FROM [dbo].[MyEntities] AS [x]
WHERE ([x].[Name] == @__name_0)
OR (([x].[Name] IS NULL) AND ((@__name_0 IS NULL))

This is hardly ideal and it is caused by the fact that, when the SQL is being generated, Entity Framework does not know what value the name parameter will have when the query is executed. If the LINQ query instead uses a literal null, or something clearly different than null, the problem does not occur. Because it doesn't know, it has to be cautious and check, if the values are identical using normal semantics or check if they are both NULL. Unfortunately, this results in some extra work for the database engine.

How to solve it…

If we are 100% sure that the values that we will be using in LINQ comparison queries, we can turn on the UseRelationalNulls flag:

protected override void OnConfiguring(
DbContextOptionsBuilder optionsBuilder)
{
    optionsBuilder.UseSqlServer(_connectionString, opt =>
    {
        //if this is present, use the simple check
        opt.UseRelationalNulls();
    });
    base.OnConfiguring(optionsBuilder);
}

If you set this, then the same query will produce this SQL instead:

SELECT [x].[Id] AS Id, [x].[Name] AS Name
FROM [dbo].[MyEntities] AS [x]
WHERE [x].[Name] == @__name_0

Of course, this will never return any records if the parameter is ever null, so be warned.

Note

In Entity Framework 6.x, this was controlled by the UseDatabaseNullSemantics property of the DbContextConfiguration class. Refer to the following link:

https://msdn.microsoft.com/en-us/library/system.data.entity.infrastructure.dbcontextconfiguration.usedatabasenullsemantics(v=vs.113).aspx.