Incorrect SQL for Any() with predicate that contains comparison with null

[bkoelman]

When using the Any() function with a predicate that checks for a property being null on an optional to-one navigation, a LEFT JOIN is produced instead of an INNER JOIN. The effect is that the returned result set contains too many matches. See the repro steps below.

This occurs with SQLite and PostgreSQL. I haven't tried other providers.

Include your code

#nullable enable

using Microsoft.EntityFrameworkCore;
using Microsoft.Extensions.DependencyInjection;
using Microsoft.Extensions.Logging;

var services = new ServiceCollection();
services.AddDbContext<AppDbContext>(options =>
{
    options.UseSqlite("Data Source=sample.db");
    options.LogTo(Console.WriteLine, LogLevel.Information);
});
var serviceProvider = services.BuildServiceProvider();

await using (var scope = serviceProvider.CreateAsyncScope())
{
    var dbContext = scope.ServiceProvider.GetRequiredService<AppDbContext>();
    await dbContext.Database.EnsureDeletedAsync();
    await dbContext.Database.EnsureCreatedAsync();

    dbContext.People.AddRange(
        new Person
        {
            // No match expected: No assignee exists.
            OwnedTodoItems =
            {
                new TodoItem()
            }
        },
        new Person
        {
            // Match expected: Assignee.Age is null.
            OwnedTodoItems =
            {
                new TodoItem
                {
                    Assignee = new Person()
                }
            }
        },
        new Person
        {
            // No match expected: Assignee.Age is non-null.
            OwnedTodoItems =
            {
                new TodoItem
                {
                    Assignee = new Person
                    {
                        Age = 21
                    }
                }
            }
        });

    await dbContext.SaveChangesAsync();
}

await using (var scope = serviceProvider.CreateAsyncScope())
{
    var dbContext = scope.ServiceProvider.GetRequiredService<AppDbContext>();

    var query = dbContext.People
        .Where(person => person.OwnedTodoItems.Any(todoItem => todoItem.Assignee!.Age == null));

    /*
      SELECT "p"."Id", "p"."Age"
      FROM "People" AS "p"
      WHERE EXISTS (
          SELECT 1
          FROM "TodoItems" AS "t"
          LEFT JOIN "People" AS "p0" ON "t"."AssigneeId" = "p0"."Id"
          WHERE "p"."Id" = "t"."OwnerId" AND ("p0"."Age" IS NULL))
    */

    var people = await query.ToArrayAsync();
    Console.WriteLine($"Found matches: {string.Join(", ", people.Select(person => person.Id))}");
}

public class TodoItem
{
    public long Id { get; set; }

    public Person Owner { get; set; } = null!;
    public Person? Assignee { get; set; }
}

public class Person
{
    public long Id { get; set; }
    public int? Age { get; set; }

    public HashSet<TodoItem> OwnedTodoItems { get; set; } = new();
    public HashSet<TodoItem> AssignedTodoItems { get; set; } = new();
}

public class AppDbContext : DbContext
{
    public DbSet<TodoItem> TodoItems => Set<TodoItem>();
    public DbSet<Person> People => Set<Person>();

    public AppDbContext(DbContextOptions options) : base(options)
    {
    }

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        modelBuilder.Entity<Person>()
            .HasMany(person => person.AssignedTodoItems)
            .WithOne(todoItem => todoItem.Assignee);

        modelBuilder.Entity<Person>()
            .HasMany(person => person.OwnedTodoItems)
            .WithOne(todoItem => todoItem.Owner);
    }
}

The code above returns two matches instead of one:

Found matches: 1, 2

Changing the LEFT JOIN to an INNER JOIN produces a single match (2), which is expected.

Include provider and version information

EF Core version: v7.0.12
Database provider: Microsoft.EntityFrameworkCore.Sqlite and Npgsql.EntityFrameworkCore.PostgreSQL
Target framework: .NET 7
Operating system: Windows
IDE: Visual Studio 2022 17.7.5

[bkoelman]

Changing the query to:

var query = dbContext.People
    .Select(person => new
    {
        Person = person,
        Count = person.OwnedTodoItems.Count(todoItem => todoItem.Assignee!.Age == null)
    });

produces:

SELECT "p"."Id", "p"."Age", (
    SELECT COUNT(*)
    FROM "TodoItems" AS "t"
    LEFT JOIN "People" AS "p0" ON "t"."AssigneeId" = "p0"."Id"
    WHERE "p"."Id" = "t"."OwnerId" AND ("p0"."Age" IS NULL)) AS "Count"
FROM "People" AS "p"

which has the same problem.

In general, I suspect that all LEFT JOINs inside a WHERE EXISTS (...) and (SELECT COUNT(*) ...) should become INNER JOINs, even if the navigations are optional.

[maumar]

note that LINQ to object also returns 2 results (when you add null ref protection, which EF does by design):

var baseline = dbContext.People.Include(x => x.OwnedTodoItems).ThenInclude(x => x.Assignee).ToList()
    .Where(person => person.OwnedTodoItems.Any(xx => xx.Assignee?.Age == null)).ToList();

[roji]

var query = dbContext.People
    .Where(person => person.OwnedTodoItems.Any(todoItem => todoItem.Assignee!.Age == null));

todoItem.Assignee in this query is an optional navigation - a TODO item may or may not have an assignee. That means that JOIN produced by EF is a LEFT JOIN rather than an INNER JOIN (you can try making it a non-optional navigation to see an INNER JOIN being produced).

Note that EF translates LINQ queries in a "null-safe" way; your LINQ query as-is would throw an exception for any TODO item without an assignee (since you're accessing Age on it without null checks). But when translated to SQL, the query executes as if the expression coalesces to NULL.

[bkoelman]

@roji I agree with what you're saying. Perhaps it's just a matter of interpretation what a "null-safe" constraint means. Instead of coalescing, it could also mean "skip if the constraint cannot be met". The .Assignee! is there only to silence the "possibly-null" compiler warning, it can be left out. The goal of the condition is to filter on to-do items that do have an assignee whose first name is null. If I change the query to:

var query = dbContext.People.Where(
    person => person.OwnedTodoItems.Any(
        todoItem => todoItem.Assignee != null && todoItem.Assignee!.Age == null));

SELECT "p"."Id", "p"."Age"
FROM "People" AS "p"
WHERE EXISTS (
    SELECT 1
    FROM "TodoItems" AS "t"
    LEFT JOIN "People" AS "p0" ON "t"."AssigneeId" = "p0"."Id"
    WHERE "p"."Id" = "t"."OwnerId" AND ("p0"."Id" IS NOT NULL) AND ("p0"."Age" IS NULL))

it still produces two matches.

[roji]

I agree with what you're saying. Perhaps it's just a matter of interpretation what a "null-safe" constraint means. Instead of coalescing, it could also mean "skip if the constraint cannot be met".

Sure. The EF interpretation is to coalesce, which is why you need a separate explicit check to exclude nulls for your case.

it still produces two matches

This doesn't look right; the "p0"."Id" IS NOT NULL is supposed to ensure that any TODO item without an assignee is filtered out. Can you please double-check?

[roji]

I'm going to go ahead and close this as the behavior by-design, but if you see anything problematic we can reopen.

[bkoelman]

This doesn't look right; the "p0"."Id" IS NOT NULL is supposed to ensure that any TODO item without an assignee is filtered out. Can you please double-check?

You're right. I misinterpreted the output Found matches: 2 as two records found, but instead it means a single record with ID=2 was found.