LINQ is Generating Extra IS NULL Condition in SQL Statement

Question

I'm writing some LINQ to fetch records based on an email, however, the generated SQL contains an additional IS NULL condition which doesn't need to be there because I am checking the parameter value for null in the code prior to adding the condition to the query.

My LINQ code is:

if (email != null)
{
    query = query.Where(r => r.Email == email);
}

The SQL condition generated from this is:

(([Extent1].[Email] = @p__linq__0) OR (([Extent1].[Email] IS NULL) AND (@p__linq__0 IS NULL)))

The

(([Extent1].[Email] IS NULL) AND (@p__linq__0 IS NULL))

doesn't need to be there as far as I can see.

Is there any way to get LINQ to omit it?

Answer 1

They're there in case email is null.

You can prevent this by setting UseDatabaseNullSemantics to true

Gets or sets a value indicating whether database null semantics are exhibited when comparing two operands, both of which are potentially nullable. The default value is false. For example (operand1 == operand2) will be translated as: (operand1 = operand2) if UseDatabaseNullSemantics is true, respectively (((operand1 = operand2) AND (NOT (operand1 IS NULL OR operand2 IS NULL))) OR ((operand1 IS NULL) AND (operand2 IS NULL))) if UseDatabaseNullSemantics is false.

There are various ways to apply this.

If you only want to apply this to a single query you can do something like this:

using(TheContext dbContext = new TheContext()) {
    dbContext.Configuration.UseDatabaseNullSemantics = true;

    ...

    if (email != null)
    {
        query = query.Where(r => r.Email == email);
    }
}

If you want to apply this to all queries:

public class TheContext : DbContext
{
    public TheContext()
    {
        this.Configuration.UseDatabaseNullSemantics = true;
    }
}

You can also change the property to be [Required]:

public class Model {
    [Required]
    public string Email { get; set; }
}