CODEWITHSUNDEEP
c#.netentity-frameworklinqlinq-to-entities
DavidWainwright
DavidWainwright

Reputation: 2935

LINQ is Generating Extra IS NULL Condition in SQL Statement

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?

Upvotes: 17

Views: 2588

Answers (2)

Salman Arshad
Salman Arshad

Reputation: 272116

In C# null == null yields true
In ANSI SQL null = null yields unknown, which is neither true nor false

LINQ translates C# semantics to SQL, so for this expression:

r.Email == email

it will generate a query similar to the following:

WHERE t.Email = @email OR (t.Email IS NULL AND @email IS NULL)

which produces the following truth table¹:

Email | @email | Result
foo   | foo    | true
null  | null   | true
foo   | bar    | false
foo   | null   | unknown
null  | foo    | unknown

which is exactly what you would expect from C#². This article explains EF null value handling in detail.

Is there any way to get LINQ to omit it?

I would not worry about it as long as (i) the query produces expected result even for null (ii) the performance is not (significantly) affected.

¹ In SQL unknown OR false yields unknown
² For the purpose of filtering only true values are considered

Upvotes: 0

Nic
Nic

Reputation: 12855

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; }
}

Upvotes: 28

Related Questions