LEFT JOIN with GroupBy subquery throws InvalidOperationException on NULL values from non-matching rows

[roji]

LEFT JOIN with GroupBy subquery throws InvalidOperationException on NULL values from non-matching rows

Moved from npgsql/efcore.pg#3801, reported by @lttruc1402.

When performing a LEFT JOIN against a GroupBy subquery containing aggregate functions (like Count()), EF Core's materializer attempts to read NULL values (from non-matching LEFT JOIN rows) into non-nullable int properties, crashing with InvalidOperationException: Nullable object must have a before the user's null-check can be evaluated.value

This is not provider- it reproduces on SQLite, SQL Server, and PostgreSQL alike.specific

Minimal repro (SQLite)

using Microsoft.EntityFrameworkCore;

await using var context = new TestContext();
await context.Database.EnsureDeletedAsync();
await context.Database.EnsureCreatedAsync();

context.PickupStatuses.AddRange(
    new PickupStatus { PickupStatusId = 1, Name = "Active" },
    new PickupStatus { PickupStatusId = 2, Name = "NoRequests" }
);
context.PickupRequests.Add(new PickupRequest { PickupStatusId = 1 });
await context.SaveChangesAsync();

var statusWithCount = context.PickupRequests
    .GroupBy(
        o => o.PickupStatusId,
        (pickupStatusId, el) => new { pickupStatusId, Count = el.Count() });

// Fails with both LeftJoin and GroupJoin+DefaultIfEmpty
var result = await context.PickupStatuses
    .LeftJoin(
        statusWithCount,
        e => e.PickupStatusId,
        e => e.pickupStatusId,
        (status, countInfo) => new { status, countInfo })
    .Select(e => new
    {
        e.status.PickupStatusId,
        Count = e.countInfo == null ? 0 : e.countInfo.Count,
    })
    .ToListAsync();

public class PickupStatus
{
    public int PickupStatusId { get; set; }
    public string Name { get; set; } = "";
}

public class PickupRequest
{
    public int Id { get; set; }
    public int PickupStatusId { get; set; }
}

public class TestContext : DbContext
{
    public DbSet<PickupStatus> PickupStatuses { get; set; }
    public DbSet<PickupRequest> PickupRequests { get; set; }

    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
        => optionsBuilder.UseSqlite("Data Source=repro.db");
}

Generated SQL

SELECT "p"."PickupStatusId", "p1"."pickupStatusId", "p1"."Count"
FROM "PickupStatuses" AS "p"
LEFT JOIN (
    SELECT "p0"."PickupStatusId" AS "pickupStatusId", COUNT(*) AS "Count"
    FROM "PickupRequests" AS "p0"
    GROUP BY "p0"."PickupStatusId"
) AS "p1" ON "p"."PickupStatusId" = "p1"."pickupStatusId"

Exception

System.InvalidOperationException: Nullable object must have a value.
   at lambda_method62(Closure, QueryContext, DbDataReader, ResultContext, SingleQueryResultCoordinator)
   at Microsoft.EntityFrameworkCore.Query.Internal.SingleQueryingEnumerable`1.AsyncEnumerator.MoveNextAsync()
   at Microsoft.EntityFrameworkCore.EntityFrameworkQueryableExtensions.ToListAsync[TSource](...)

Notes

• Also reproduces with the traditional GroupJoin + DefaultIfEmpty pattern
• Not a pre-existing since at least EF Core 5.xregression
• Possibly related to Nullable object must have a value thrown for a query with DefaultIfEmpty() #30915 and Result of left joining a nullable nominal type should be null (i.e. no instance) and not an instance with all null property values #22517

[roji]

AI Triage

The below is an AI-generated analysis and may contain inaccuracies.

Analysis

This is not specific to the Npgsql/PostgreSQL it is a general EF Core bug that reproduces identically on SQLite (and presumably all relational providers). The count(*)::int cast mentioned in the original report is just PostgreSQL-specific rendering; on SQLite the same query uses COUNT(*) and produces the same error.provider

The issue also reproduces with the traditional GroupJoin + DefaultIfEmpty pattern (not just the new .NET 10 LeftJoin operator), confirming it's a long-standing EF Core behavior.

Root cause: When a LEFT JOIN is performed against a GroupBy subquery containing aggregate functions (like Count()), EF Core generates SQL that correctly uses LEFT JOIN. However, for non-matching rows, the aggregate columns return NULL. EF Core's materializer then attempts to read these NULL values into non-nullable int properties, crashing with InvalidOperationException: Nullable object must have a before the user's null-check (e.countInfo == null ? 0 : e.countInfo.Count) can be evaluated.value

Possible Duplicates

This is a duplicate of / closely related to the following existing EF Core issues:

• # "Nullable object must have a value thrown for a query with DefaultIfEmpty()" (Backlog)30915
• # "Result of left joining a nullable nominal type should be null (i.e. no instance) and not an instance with all null property values" (milestoned for 11.0.0, assigned, priority-bug)22517

Regression?

No. This is a pre-existing EF Core limitation that has been open since at least EF Core 5.x.

[MihuBot]

I'm a bot. Here are possible related and/or duplicate issues (I may be wrong):

• Bug: EF Core LEFT JOIN with GroupBy subquery throws InvalidOperationException on NULL values from non-matching rows #38054
• LEFT JOIN with GROUP BY: System.InvalidOperationException: Nullable object must have a value. #12355
• Must I use nullable property to determine whether the type is null when left join a IQueryable<anonymous>(sub query, gourp by)? #28273

[shaofing]

#30915