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...