Entity framework adds an extra condition on where clause

Question

I have identified that when the following expression is executed:

int aNum = 52;
var myArtifacts = mydbcontext.artifacts.Where(a => a.ParentID == aNum ).ToList();

on mysql the query executed is:

SELECT
  `Extent1`.`ID`, 
  `Extent1`.`ParentID`
FROM `artifacts` AS `Extent1`
WHERE ((`Extent1`.`ParentID` = 52) AND (52 IS NOT NULL));

Can anyone explain please why this last extra condition is added?

AND (52 IS NOT NULL))

Answer 1

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

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.

If the current behaviour is bothering you, consider setting UseDatabaseNullSemantics to true.

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

or

myDbContext.Configuration.UseDatabaseNullSemantics = true;