When you compare numbers, you know that 5 equals 5. But C#, Entity Framework, SQL and many programming languages have a special value called “null”. It’s something unknown. So when you compare a definite number, for example 5, with null, the result will be “false”, because 5 and unknown is not the same.
But the situation differs when you compare null with null. Guess will it be true or false? But the right answer is: It depends… And now I’ll tell you why.
T-SQL has a concept that unknown value NULL is not equal to another unknown value NULL. At the same time C# and Entity Framework believe that two nulls are equal. This comes from the reference types where null means that a pointer does not reference to any variable. So when 2 pointers are null, so they are equal, because they don’t reference to any variable.
To better understand the differences between Entity Framework and C# I created a simple test to compare values. Here is my C# project StringEqualityTest that you can download and run in your own pace.
The results are the following:
| Left operand |
Right operand |
C# |
Entity Framework |
Explanation |
| “A” |
“A” |
true |
true |
Exact match |
| “B” |
“b” (lowercase) |
false |
true* |
EF uses SQL Server comparison rules |
| “B” |
“B ” (with trailing space) |
false |
true |
EF uses SQL Server comparison rules |
| “” |
“” (empty string) |
true |
true |
Exact match |
| null |
null |
true |
true |
EF uses C# rules |
|
|
|
* – not always |
|
Let’s look deeper into what’s going on.
When Entity Framework compares two not null strings, it applies rules defined in SQL Server. It seems that EF sends a query and merely receives the result from SQL Server. And all the rules come from SQL Server. That’s the reason why SQL Server discards trailing spaces (look at “B” = “B “). Also it uses a collation for comparison and ordering. In my case it’s a Cyrillic_General_CI_AS, CI means Case Insensitive, AS means Accent Sensitive. So “B” = “b”. If you have another collation, the result might be different.
Just compare these examples with C# rules where “B” <> “b”, and “B” <> “B “. C# always returns the same result.
But when EF compares null strings, it shows they are equal like C# do. To do this, EF generate a special query for SQL Server to receive the result in C# logic. When you look at SELECT command generated by EF, you’ll find something interesting.
|
|
SELECT 1 AS [C1], [Extent1].[Code] AS [Code], [Extent1].[Name] AS [Name], [Extent2].[Code] AS [Code1], [Extent2].[Name] AS [Name1] FROM [dbo].[FirstEntities] AS [Extent1] INNER JOIN [dbo].[SecondEntities] AS [Extent2] ON ([Extent1].[Code] = [Extent2].[Code]) OR (([Extent1].[Code] IS NULL) AND ([Extent2].[Code] IS NULL)) |
EF adds a second condition when both operands are null. That’s why EF shows the result in C# style while SQL Server executes all the comparison as it did forever.
Entity Framework has a dedicated parameter that could change the null comparison logic to be implemented by SQL Server. In EF 6.0 it’s a UseDatabaseNullSemantics. When you set it to true (the default is false), you’ll get a SQL Server rules for null equality. Some previous versions of EF uses an opposite parameter UseCSharpNullComparisonBehavior.
This time the SQL query would not have an additional condition for nulls.
|
|
SELECT 1 AS [C1], [Extent1].[Code] AS [Code], [Extent1].[Name] AS [Name], [Extent2].[Code] AS [Code1], [Extent2].[Name] AS [Name1] FROM [dbo].[FirstEntities] AS [Extent1] INNER JOIN [dbo].[SecondEntities] AS [Extent2] ON [Extent1].[Code] = [Extent2].[Code] |
UPDATE:
I’ve missed an important option that influence SQL behavor (thanks to Arthur Zubarev for his valuable comment). There’s an option ANSI_NULLS. If you set it to false, so SQL Server would think that nulls are equal. But you can hardly see it in action because SQL Server Native Client driver automatically set it to ON when connecting.
