none
EF Generated SQL - Evaluating Parameters for NULL RRS feed

  • Question

  • I have noticed using EF that when creating LINQ statements searching for a specific value on a column the generated SQL also evaluates for null and I cannot figure out why.

    Take the following example:

    context.MyTable.Where(t => t.MyColumn == "MyValue" && someStringList.Contains(t.MyOtherColumn)).ToList()

    The SQL that is generated and executed contains extra or conditions that check for NULL values.

    (MyColumn = 'MyValue' OR (MyColumn is NULL AND @p__linq__0 is NULL))

    AND

    MyOtherColumn IN ('string1','string2','...) AND MyOtherColumn IS NOT NULL

    The reason that this is an issue is that I cannot utilize filtered indexes based on MyColumn = 'MyValue'.  I am curious as to the reason for this extra check for NULL when I don't evaluate for NULL.

    Friday, August 28, 2015 7:19 PM

Answers

  • Entity Framework by default will generate SQL code that has C# null comparison semantics. 

    You can override that to use database null semantics. Note that this might potentially behave differently to the C# null semantics since now Entity Framework will generate simpler SQL that exposes the way the database engine handles null values. Database null semantics can be activated per-context with one single configuration line against the context configuration:

     context.Configuration.UseDatabaseNullSemantics = true;


    Happy Coding.

    Monday, August 31, 2015 4:40 AM

All replies

  • Maybe the database schema has a null definition for a given table column? Maybe the property in the entity/object has a null value definition?
    Friday, August 28, 2015 10:06 PM
  • Entity Framework by default will generate SQL code that has C# null comparison semantics. 

    You can override that to use database null semantics. Note that this might potentially behave differently to the C# null semantics since now Entity Framework will generate simpler SQL that exposes the way the database engine handles null values. Database null semantics can be activated per-context with one single configuration line against the context configuration:

     context.Configuration.UseDatabaseNullSemantics = true;


    Happy Coding.

    Monday, August 31, 2015 4:40 AM