Comparison: Entity Framework vs C#. When Equality Operators Are Not Equal

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.

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.

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.

One Reply to “Comparison: Entity Framework vs C#. When Equality Operators Are Not Equal”

  1. I didn’t try, but the rule item
    “B” “b” (lowercase) false true EF uses SQL Server comparison rules
    Rather shall be “it depends”.
    E.g. if the collation is not case sensitive (default SQL_Latin1_General_CP1_CI_AS) then this rule is correct.
    But when the collation IS case sensitive it will be “false” “false” for both.
    Re the NULLs, they are design mistake, when J.T.Date in London’s IBM lab said that his boss was very angry. This is the lab where IBM conceived SQL as a language. Do you know OS2? Well SQL survived.
    Furthermore, the SQL ANSII NULL treatment setting needs to be discussed https://msdn.microsoft.com/en-us/library/ms188048.aspx?f=255&MSPPError=-2147217396 just discover how WHERE NULL = NULL can be TRUE.

Leave a Reply

Your email address will not be published. Required fields are marked *