EF core 6 selecting null values despite where clause asking for not null

user_7534341 0
Viewed 656

I have a Linq2Sql Query like this:

Parent.Include(p => p.Children)
  .Where(p => p.Children.Any(c => c.SomeNullableDateTime == null)
    && p.Children
        .Where(c => c.SomeNullableDateTime == null)
        .OrderBy(c => c.SomeInteger)
        .First()
        .SomeOtherNullableDateTime != null
  )
  .Select(p => p.Children
        .Where(c => c.SomeNullableDateTime == null)
        .OrderBy(c => c.SomeInteger)
        .First()
        .SomeOtherNullableDateTime)
  .ToList();

This worked fine until moving from EF core 5 to EF core 6. With EF core 6 the result list contains some null values (which should not be the case because the where condition asks for not null). Is there some breaking change / limitation in EF core 6 I'm not aware of or is this simply a bug?

Update: This is an excerpt of the output

enter image description here

Update 2: This is the generated SQL Statement

SELECT(
    SELECT TOP(1)[p1].[SomeOtherNullableDateTime]
    FROM[Children] AS[p1]
    WHERE([p].[Id] = [p1].[ParentId]) AND[p1].[SomeNullableDateTime] IS NULL
    ORDER BY[p1].[SomeInteger])
FROM[Parent] AS[p]
WHERE EXISTS(
    SELECT 1
    FROM[Children] AS [c]
    WHERE ([p].[Id] = [c].[ParentId]) AND[c].[SomeNullableDateTime] IS NULL) AND EXISTS(
   SELECT 1
   FROM[Children] AS [c0]
    WHERE ([p].[Id] = [c0].[ParentId]) AND[c0].[SomeNullableDateTime] IS NULL)
GO

So it looks like the problem is that SomeOtherNullableDateTime (which is supposed to be not null) is not even included in the where clause of the generated SQL.

Update 3: This is the SQL EF core 5 (correctly) generates

SELECT (
    SELECT TOP(1) [c].[SomeOtherNullableDateTime]
    FROM [Children] AS [c]
    WHERE ([p].[Id] = [c].[ParentId]) AND [c].[SomeNullableDateTime] IS NULL
    ORDER BY [c].[SomeInteger])
FROM [Parent] AS [p]
WHERE EXISTS (
    SELECT 1
    FROM [Children] AS [c0]
    WHERE ([p].[Id] = [c0].[ParentId]) AND [c0].[SomeNullableDateTime] IS NULL) AND (
    SELECT TOP(1) [c1].[SomeOtherNullableDateTime]
    FROM [Children] AS [c1]
    WHERE ([p].[Id] = [c1].[ParentId]) AND [c1].[SomeNullableDateTime] IS NULL
    ORDER BY [c1].[SomeInteger]) IS NOT NULL
GO
ef-core-6.0 entity-framework-core
3 Answers

Looks like EF Core 6.0 query translation bug. The same happens if you use the "more natural" way of writing such query

var query = db.Set<Parent>()
    .Select(p => p.Children
        .Where(c => c.SomeNullableDateTime == null)
        .OrderBy(c => c.SomeInteger)
        .FirstOrDefault())
    .Where(c => c.SomeOtherNullableDateTime != null)
    .Select(c => c.SomeOtherNullableDateTime);

The generated SQL

SELECT (
    SELECT TOP(1) [c0].[SomeOtherNullableDateTime]
    FROM [Child] AS [c0]
    WHERE ([p].[Id] = [c0].[ParentId]) AND [c0].[SomeNullableDateTime] IS NULL
    ORDER BY [c0].[SomeInteger])
FROM [Parent] AS [p]
WHERE EXISTS (
    SELECT 1
    FROM [Child] AS [c]
    WHERE ([p].[Id] = [c].[ParentId]) AND [c].[SomeNullableDateTime] IS NULL)

is also missing IS NOT NULL criteria, so you might include this scenario in the bug report.

The equivalent pattern (with SelectMany + Take(1) instead of Select + FirstOrDefault())

var query = db.Set<Parent>()
    .SelectMany(p => p.Children
        .Where(c => c.SomeNullableDateTime == null)
        .OrderBy(c => c.SomeInteger)
        .Take(1))
    .Where(c => c.SomeOtherNullableDateTime != null)
    .Select(c => c.SomeOtherNullableDateTime);

(same as suggested at the meantime by @Svyatoslav), generates different SQL

SELECT [t0].[SomeOtherNullableDateTime]
FROM [Parent] AS [p]
INNER JOIN (
    SELECT [t].[ParentId], [t].[SomeNullableDateTime], [t].[SomeOtherNullableDateTime]
    FROM (
        SELECT [c].[ParentId], [c].[SomeNullableDateTime], [c].[SomeOtherNullableDateTime], ROW_NUMBER() OVER(PARTITION BY [c].[ParentId], [c].[SomeNullableDateTime] ORDER BY [c].[SomeInteger]) AS [row]
        FROM [Child] AS [c]
    ) AS [t]
    WHERE [t].[row] <= 1
) AS [t0] ON ([p].[Id] = [t0].[ParentId]) AND [t0].[SomeNullableDateTime] IS NULL
WHERE [t0].[SomeOtherNullableDateTime] IS NOT NULL

which has IS NOT NULL condition, but now the inner subquery looks wrong, since it selects every first child ordered by something, then applies IS NULL criteria, while the LINQ query requests to first apply the IS NULL criteria, then select the first child ordered by something. So you might include this use case in the bug report as well.

All these queries, including the one from the OP, work correctly (generate correct SQL) in EF Core 5.0.

user_52025630

The development team on GitHub has confirmed that there are two different bugs that cause these problems:

https://github.com/dotnet/efcore/issues/26744

https://github.com/dotnet/efcore/issues/26756

Unfortunately they have stated that these bugs will not be fixed in the 6.0.1 release scheduled for december but earliest with another release scheduled for February 2022.

Since these bugs cause EF core 6 to quietly return wrong results and there is a good chance of many users messing up their data or making decisions based on wrong data (because no one will check all Linq2SQL queries for correct SQL generation !?) I recommend to not use EF core 6 for now!

This might be regarded as opinion-based but please don't delete this answer but leave it as a warning for fellow developers!

Update: There are fixes for these issues now:

https://github.com/dotnet/efcore/pull/27284

https://github.com/dotnet/efcore/pull/27292

They have been approved to be released with version 6.0.3 which is planned for March 2022.

user_75343410

While it can be regression, I would suggest to rewrite query in effective and more predictable way:

var query =
    from p in Parent
    from c in p.Children
        .Where(c.SomeNullableDateTime == null)
        .OrderBy(c => c.SomeInteger)
        .Take(1)
    where c.SomeOtherNullableDateTime != null
    select c.SomeOtherNullableDateTime;
user_106463160
Related