NULL handling in dbcontext and objectcontext

Question

I have this simple LINQ query

from e in Employees 
where e.DesignationID !=558
select e

Here DesignationID is a nullable field:

In objectcontext the query is translated to:

SELECT 
[Extent1].[EmployeeID] AS [EmployeeID], 
[Extent1].[EmployeeCode] AS [EmployeeCode], 
[Extent1].[EmployeeName] AS [EmployeeName],
[Extent1].[DesignationID] AS [DesignationID] 
FROM [dbo].[setupEmployees] AS [Extent1]
WHERE 558 <> [Extent1].[DesignationID]

While the same query in dbcontext it is translated to:

 SELECT 
    [Extent1].[EmployeeID] AS [EmployeeID], 
    [Extent1].[EmployeeCode] AS [EmployeeCode], 
    [Extent1].[EmployeeName] AS [EmployeeName],
    [Extent1].[DesignationID] AS [DesignationID] 
    FROM [dbo].[setupEmployees] AS [Extent1]
 WHERE  NOT ((558 = [Extent1].[DesignationID]) AND ([Extent1].[DesignationID] IS NOT NULL))

Why does objectcontext handle NULL differently than dbcontext?

Answer 1

This behavior is configurable, so most likely it's a matter of a different default (I don't know why the default is different).

The control is provided by the DbContextConfiguration.UseDatabaseNullSemantics property:

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.

To get the same translation as in your first example, you should set it to true (for instance inside your db context constructor):

public YourDbContext()
{
    // ...
    this.Configuration.UseDatabaseNullSemantics = true;
}