Entity Framework: Any(..)-filtering with == matches NULL after upgrading from EF5 to EF6

As part of upgrading my ASP.NET MVC project to target .NET Framework v4.8 and ASP.NET MVC v5.2.7, Entity Framework was also upgraded from v5.0.0 to v6.4.4; and after that I noticed a weird change using the Any() construct.

BTW I also followed the steps here: https://learn.microsoft.com/en-us/ef/ef6/what-is-new/upgrading-to-ef6

I have this piece of code in my project (same code before and after the upgrade)

[HttpPost]
public ActionResult Create(Course course)
{
    ...
    if (context.Courses.Any(c => c.ExternalCourseNumber == course.ExternalCourseNumber))
    {
        throw new Exception(Resources.Global.ExternalCourseIdAlreadyExists);
    }
    ...
}

It is a validation, that is part of creating a new Course. The Course object is the model passed to the Create method, and the field ExternalCourseNumber is left empty on the webform, and so course.ExternalCourseNumber has the value null, when I debug in Visual Studio.

The point is, that if an ExternalCourseNumber has been provided, it must not exist for any existing course, but when it has not been provided, I don't want to throw a validation error.

Prior to the upgrade, this validation would not throw a validation error, when ExternalCourseNumber is left empty/null. After the upgrade, my code throws a validation error.

I.e. it seems to me that:

Courses.Any(c => c.ExternalCourseNumber == course.ExternalCourseNumber)

have changed behaviour when comparing to null after the upgrade....and that is what puzzles me?!?

I know how to fix the issue by also testing for the null-value, but the change in behaviour worries me, as it may also affect other functionality, so I would like to understand what happened?

Did the == operator start to also match NULL-values or am I missing something?

=== UPDATE: SQL generated from EF6 =====

I managed to use the EF6 logging framework in order to see the SQL:

SELECT 
    CASE WHEN ( EXISTS (SELECT 
        1 AS [C1]
        FROM [dbo].[Courses] AS [Extent1]
        WHERE ([Extent1].[ExternalCourseNumber] = @p__linq__0) OR (([Extent1].[ExternalCourseNumber] IS NULL) AND (@p__linq__0 IS NULL))
    )) THEN cast(1 as bit) ELSE cast(0 as bit) END AS [C1]
    FROM  ( SELECT 1 AS X ) AS [SingleRowTable1]

...so that explains why the query will include courses where ExternalCourseNumber is null. But why/when did that change? Why doesn't EF5 behave in the same way? That seems like a "breaking change"?

PS I can't figure out how to see the SQL generated in EF5...any suggestions?

Solution

This has to do with a breaking change in database null semantics.

In EF6, a query like context.Courses.Any(c => c.ExternalCourseNumber == course.ExternalCourseNumber) will translate into something like:

WHERE ([Extent1].[ExternalCourseNumber] = @p__linq__0) 
    OR (([Extent1].[ExternalCourseNumber] IS NULL) AND (@p__linq__0 IS NULL))

(where @p__linq__0 is a (n)varchar variable equal to null).

In EF5 this will be something like:

WHERE [Extent1].[ExternalCourseNumber] = @p__linq__0

The second query compares null to null, which in SQL is undefined, and the query returns nothing.

In EF6 this problem was fixed by making the comparison semantics equal to the semantics in C#, where null == null returns true. That required an additional OR predicate. The first query (EF6) returns courses where ExternalCourseNumber is null.

It's surprising that this breaking change isn't clearly documented. In EF5, ObjectContext already had a setting ObjectContext.ContextOptions.UseCSharpNullComparisonBehavior by which the behavior could be changed. The default was false; setting it to true turned the SQL translation into that of the first query.

In EF6 this setting was moved to the preferred DbContext as DbContext.Configuration.UseDatabaseNullSemantics, so, in fact, the opposite of UseCSharpNullComparisonBehavior. The breaking change was that the default was also false, probably because the C# semantics were deemed to be the preferred behavior, as it would be what most EF developers would expect.

In your case I wouldn't change UseDatabaseNullSemantics, but simply add an extra check if course.ExternalCourseNumber is not equal to null, because it makes the code more self-explanatory.