Compare NULLs in database as in C# in Entity Framework
Roughly two years ago I blogged about differences in NULLs handling in databases and in (some) programming languages (C# in particular). But Entity Framework 5 (in .NET 4.5) (lost?) comes with handy switch. It’s UseCSharpNullComparisonBehavior. What this basically does is ensure when you compare nullable fields to be handled like in C#, aka null == null and not like in SQL where NULL compared to anything is NULL or false when boolean value is needed.
Let’s have a look at the diffences. Simple code:
int? i = default(int);
using (testEntities c = new testEntities())
{
c.ContextOptions.UseCSharpNullComparisonBehavior = true;
Console.WriteLine((c.Details.Where(x => x.MasterID != i) as ObjectQuery).ToTraceString());
}
using (testovaciEntities c = new testovaciEntities())
{
c.ContextOptions.UseCSharpNullComparisonBehavior = false;
Console.WriteLine((c.Details.Where(x => x.MasterID != i) as ObjectQuery).ToTraceString());
}
The first query creates “magic” SQL:
SELECT
[Extent1].[ID] AS [ID],
[Extent1].[ID_MASTER] AS [ID_MASTER],
[Extent1].[BAR] AS [BAR]
FROM [dbo].[DETAIL] AS [Extent1]
WHERE NOT ((([Extent1].[ID_MASTER] = @p__linq__0) AND ( NOT ([Extent1].[ID_MASTER] IS NULL OR @p__linq__0 IS NULL))) OR (([Extent1].[ID_MASTER] IS NULL) AND (@p__linq__0 IS NULL)))
While the other expected one:
SELECT
[Extent1].[ID] AS [ID],
[Extent1].[ID_MASTER] AS [ID_MASTER],
[Extent1].[BAR] AS [BAR]
FROM [dbo].[DETAIL] AS [Extent1]
WHERE [Extent1].[ID_MASTER] <> @p__linq__0
What the first one is doing is handling the case where C# null (in parameter) can be compared to database NULL to fix-up the logic.
If you’re not familiar with NULLs in database this can be tricky to handle correctly, especially if you have negations, Alls and Anys nested inside query. Hence setting this property to true can save you hours of debugging and wondering what’s going on.
