DbContext: Expose UseCSharpNullComparisonBehavior


Talk to Diego or Arthur before doing this

"Addressing the mismatch in null comparisions between LINQ to Entities and LINQ to Objects is one of the top customers asks we have heard. An example of the issues is that the following query returns empty results although there are Products with null CategoryId:
int? cid = null;
var q = 
    from p in db.Products
    where p.CategoryId == cid
    select p;
var count = q.Count();
The workaround is to explicitly add comparisons for a null constant in the query. When LINQ to Entities sees a comparision against a null constants, i.e. 'a == null' it translates it into a DbExpression with the meaning 'a IS NULL' for the server query.

We added a fix but the new behavior requires setting a flag in ObjectContextOptions which is off by default for compatibility. The change was extensively tested at the time for its impact in query translation and performance, and the plan was always to enable the falg by default in DbContext during initialization of ObjectContext but we missed making the change in DbContext.

This is the main improvement in LINQ to Entities in EF 5 besides performance.

We discovered that setting the flag breaks simple queries in SQL Compact--details in email below. Considering this bug blocked for now.

We have encountered an issue using EF in .NET 4.5 with SQL Compact. This seems like a bug in either SQL Compact itself or in the EF provider for SQL Compact.

Details: we have introduced a new option in ObjectContext.ContextOptions that changes the way nulls are compared in queries generated by LINQ to Entities. A simple example is:
objectContext.ContextOptions.UseCSharpNullComparisonBehavior = true;

var bar = ""bar"";
objectContext.CreateObjectSet<Foo>().Any(f => f.Bar == bar);
where Foo is the only entity type in the model and looks like this:
public class Foo
    public int Id { get; set; }
    public string Bar { get; set; }
When we run this against SQL Compact 4.0 in recent Visual Studio 11 Beta builds the SQL generated by the SQL Compact EF provider is as follows:
        1 AS [C1]
        FROM [Foos] AS [Extent1]
        WHERE (([Extent1].[Bar] = @p__linq__0) AND ( NOT (([Extent1].[Bar] IS NULL) OR (@p__linq__0 IS NULL)))) OR (([Extent1].[Bar] IS NULL) AND (@p__linq__0 IS NULL))
)) THEN cast(1 as bit) WHEN ( NOT EXISTS (SELECT
        1 AS [C1]
        FROM [Foos] AS [Extent2]
        WHERE (([Extent2].[Bar] = @p__linq__0) AND ( NOT (([Extent2].[Bar] IS NULL) OR (@p__linq__0 IS NULL)))) OR (([Extent2].[Bar] IS NULL) AND (@p__linq__0 IS NULL))
)) THEN cast(0 as bit) END AS [C1]
FROM  ( SELECT 1 AS X ) AS [SingleRowTable1]
This fails with the following exception:
System.Data.EntityCommandExecutionException: An error occurred while executing the command definition. See the inner exception for details.

       System.Data.EntityCommandExecutionException: An error occurred while executing the command definition. See the inner exception for details. ---> System.Data.SqlServerCe.SqlCeException: The specified argument value for the function is not valid. [ Argument # = 1,Name of function(if known) = isnull ]

       at System.Data.SqlServerCe.SqlCeCommand.ProcessResults(Int32 hr)

       at System.Data.SqlServerCe.SqlCeCommand.CompileQueryPlan()

       at System.Data.SqlServerCe.SqlCeCommand.ExecuteCommand(CommandBehavior behavior, String method, ResultSetOptions options)

       at System.Data.SqlServerCe.SqlCeCommand.ExecuteReader(CommandBehavior behavior)

       at System.Data.SqlServerCe.SqlCeMultiCommand.ExecuteReader(CommandBehavior behavior)

       at System.Data.SqlServerCe.SqlCeMultiCommand.ExecuteDbDataReader(CommandBehavior behavior)

       at System.Data.Common.DbCommand.ExecuteReader(CommandBehavior behavior)

       at System.Data.EntityClient.EntityCommandDefinition.ExecuteStoreCommands(EntityCommand entityCommand, CommandBehavior behavior)

          --- End of inner exception stack trace ---

       at System.Data.EntityClient.EntityCommandDefinition.ExecuteStoreCommands(EntityCommand entityCommand, CommandBehavior behavior)

       at System.Data.Objects.Internal.ObjectQueryExecutionPlan.Execute[TResultType](ObjectContext context, ObjectParameterCollection parameterValues)

       at System.Data.Objects.ObjectQuery`1.GetResults(Nullable`1 forMergeOption)

       at System.Data.Objects.ObjectQuery`1.System.Collections.Generic.IEnumerable<T>.GetEnumerator()

       at System.Linq.Enumerable.Single[TSource](IEnumerable`1 source)

       at System.Data.Objects.ELinq.ObjectQueryProvider.<GetElementFunction>b__3[TResult](IEnumerable`1 sequence)

       at System.Data.Objects.ELinq.ObjectQueryProvider.ExecuteSingle[TResult](IEnumerable`1 query, Expression queryRoot)

       at System.Data.Objects.ELinq.ObjectQueryProvider.System.Linq.IQueryProvider.Execute[S](Expression expression)

       Data\Entity\Internal\Linq\DbQueryProvider.cs(82,0): at System.Data.Entity.Internal.Linq.DbQueryProvider.Execute[TResult](Expression expression)

       at System.Linq.Queryable.Any[TSource](IQueryable`1 source, Expression`1 predicate)

       ProductivityApi\SimpleScenariosForSqlCe.cs(351,0): at ProductivityApiTests.SimpleScenariosForSqlCe.Can_perform_simple_Any_query_in_SqlCe()
We get the same exception when trying to execute the generated SQL outside of EF. In addition, we can also reproduce this exception using Entity SQL even without the changes for the new flag. For example:
using (SqlCeIssueEntities ctx = new SqlCeIssueEntities())
           var queryString = @""select value c from SqlCeIssueEntities.Tests as c where @myVar is null or c.name = @myVar"";

           ObjectQuery<Test> contactQuery = new ObjectQuery<Test>(queryString, ctx);
           contactQuery.Parameters.Add(new ObjectParameter(""myVar"", ""TestVal""));
           foreach (Test result in contactQuery)
               Console.WriteLine(""Last Name: {0};"", result.name);
See http://entityframework.codeplex.com/workitem/287 for more details on the underlying issue.
Closed Apr 19, 2013 at 2:24 AM by maumar
Existing tests provide enough coverage. Added small test in 7155e70. Closing


emilcicos wrote Apr 15, 2013 at 10:01 PM

The flag has been exposed on DbContextConfiguration as UseDatabaseNullSemantics, which has the opposite effect of UseCSharpNullComparisonBehavior.