Bug: EF Core LEFT JOIN with GroupBy subquery throws InvalidOperationException on NULL values from non-matching rows

[lttruc1402]

Bug: EF Core LEFT JOIN with GroupBy subquery throws InvalidOperationException on NULL values from non-matching rows

Component: Query / EF Core
Severity: High
Reported: 2026-04-06

---

Environment

Platform	.NET / EF Core
EF Core Provider	Npgsql.EntityFrameworkCore.PostgreSQL 10.0.1
Database	PostgreSQL
Schema	ops.pickup_status / ops.pickup_request
Operation	LEFT JOIN + GroupBy subquery

---

Description

When performing a LEFT JOIN between pickup_status and a GroupBy subquery on pickup_request, EF Core generates SQL with count(*)::int (non-nullable cast). For status records that have no matching pickup_request rows within the given date filter, the "Count" column returns NULL from the LEFT JOIN. EF Core then attempts to map NULL into a non-nullable int and throws an exception. Additionally, EF Core projects an extra column (p1."pickupStatusId") into the subquery that was not explicitly selected in C#.

---

Code to Reproduce

var statusWithCount = _queryService.AsQueryable(filterPickup, true)
    .GroupBy(
        o => o.PickupStatusId,
        (pickupstatusId, el) => new { pickupstatusId, Count = el.Count() });

var result = await _queryService.AsQueryable(filterStatus, true)
    .LeftJoin(
        statusWithCount,
        e => e.PickupStatusId,
        e => e.pickupstatusId,
        (status, debitNote) => new { status, debitNote }
    )
    .Select(e => new
    {
        e.status.PickupStatusId,
        C = e.debitNote == null ? 0 : e.debitNote.Count,
    })
    .ToListAsync(cancellationToken: cancellationToken);

---

Steps to Reproduce

1. Build a GroupBy subquery on pickup_request using (pickupStatusId, el) => new { pickupstatusId, Count = el.Count() }
2. LEFT JOIN the subquery against pickup_status on PickupStatusId
3. Select PickupStatusId and Count with a null-check e.debitNote == null ? 0 : e.debitNote.Count
4. Call ToListAsync()
5. Ensure at least one status has no matching pickup_request rows within the filter range

---

Actual SQL Generated

SELECT p.pickup_status_id, p1."pickupStatusId", p1."Count"
FROM ops.pickup_status AS p
LEFT JOIN (
    SELECT p0.pickup_status_id AS "pickupStatusId", count(*)::int AS "Count"
    FROM ops.pickup_request AS p0
    WHERE p0.created_time > @fromDate AND p0.created_time <= @toDate
    GROUP BY p0.pickup_status_id
) AS p1 ON p.pickup_status_id = p1."pickupStatusId"

For pickup_status_id = 50, both p1."pickupStatusId" and p1."Count" are NULL. Note that p1."pickupStatusId" is an extra projected column not explicitly selected in C# — EF Core adds it internally as the JOIN key.

---

Exception

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

---

Root Cause

EF Core translates el.Count() to count(*)::int — a non-nullable PostgreSQL cast. After the LEFT JOIN, this column is NULL for non-matching rows. EF Core's materializer calls .Value on a Nullable<int> that is null, causing the exception. The null-check in C# (e.debitNote == null ? 0 : ...) has no effect because the crash occurs during materialization, before the object is constructed.

---

Attempted Workarounds (Ineffective)

• Casting (int?)el.Count() in the GroupBy selector — EF Core still generates count(*)::int
• Adding null-check e.debitNote == null ? 0 : e.debitNote.Count in Select — crash happens before object mapping
• Rewriting with GroupJoin + SelectMany — EF Core does not support filter conditions in the ON clause, producing incorrect results

---

Tags

ef-core left-join group-by nullable postgresql linq pickup-status

[lttruc1402]

[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 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 (dotnet/efcore)

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

• https://github.com/dotnet/efcore/issues/ "Nullable object must have a value thrown for a query with DefaultIfEmpty()" (Backlog)30915
• https://github.com/dotnet/efcore/issues/ "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.

Minimal repro (SQLite, EF Core 10 preview)

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

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() });

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();

foreach (var r in result)
    Console.WriteLine($"StatusId={r.PickupStatusId}, Count={r.Count}");

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")
            .LogTo(Console.WriteLine, LogLevel.Information)
            .EnableSensitiveDataLogging();
}

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()

[lttruc1402]

[roji]

This is an EF Core issue, not specific to the Npgsql provider. Moved to dotnet/efcore#38055. @ you can follow the new issue for updates.lttruc1402