Correlated FirstOrDefault subquery over a Concat/Union (keyless) source inside a collection projection: silently missing collection elements (single query) or ArgumentOutOfRangeException (split query)

[ulissesqueiroz]

Bug description

When the projection of a correlated collection contains a single-result subquery (.Where(...).Select(...).FirstOrDefault()) whose source is a keyless set operation — Concat/Union of two projected entity sets — EF Core loses the identifiers of the outer collection's elements and, instead of failing translation, produces:

• Single query: silently wrong results — the collection materializes at most one element per parent (consecutive rows are bucketed by the parent identifier only, because the element identifier is empty).
• Split query: ArgumentOutOfRangeException: Index was out of range at materialization time.

Output of the attached repro (EF Core 10.0.9, Sqlite in-memory; expected: 1 order with 2 lines):

[union source, single query] lines materialized: 1 (expected 2) -> [line 1: tax 10]
[union source, split query ] threw ArgumentOutOfRangeException: Index was out of range. Must be non-negative and less than the size of the collection. (Parameter 'index')
[keyed source, single query] lines materialized: 2 (expected 2) -> [line 1: tax 10, line 2: tax ]
[keyed source, split query ] lines materialized: 2 (expected 2) -> [line 1: tax 10, line 2: tax ]

The last two lines show the contrast: replacing the union source with a single keyed DbSet projection (same subquery shape otherwise) produces correct results in both modes. The trigger is specifically the keyless set-operation source.

Mechanism, visible in the compiled query plan (CoreEventId.QueryExecutionPlanned, event 10107). With the union source, for the Lines collection:

parentIdentifier:  ... => new object[]{ (object)(int?)dataReader.GetInt32(0) }   // arity 1
childIdentifier:   ... => new object[]{ }                                        // EMPTY
identifierValueComparers: ... => new Func<object, object, bool>[]{ }             // EMPTY

With the keyed source, childIdentifier is new object[]{ (object)(int?)dataReader.GetInt32(4) } and there is one comparer — correct. In single-query mode the same emptiness shows up as selfIdentifier: new object[]{ }, so PopulateCollection dedups rows by the parent identifier and distinct elements collapse. In split-query mode, CompareIdentifiers iterates over the parent identifier's length while indexing into the empty comparers array and throws (RelationalShapedQueryCompilingExpressionVisitor.ClientMethods.cs — the code even remarks the lengths "should be same unless there is a bug in code"). The generated SQL confirms the missing identity: the single query's ORDER BY contains only the parent's key column, no element columns.

Expected behavior: either correct results or, if this shape genuinely cannot be identified, the existing translation-time error (InsufficientInformationToIdentifyElementOfCollectionJoin — "Unable to translate a collection subquery in a projection since either parent or the subquery doesn't project necessary information required to uniquely identify it and correctly generate results on the client side..."), which is how sibling shapes fail today. Silently dropping collection elements is the dangerous outcome here — nothing signals the data is incomplete.

Related issues (searched; none seems to be this exact defect): #26078 states that keyless sources in nested-collection scenarios are expected to throw due to lack of identifiers — this shape escapes that validation and proceeds with an empty identifier instead; #33485 tracks improving the same identifier validation; #34728 was the same exception in split queries but caused by identity resolution and was fixed in 9.0.0 (this repro fails on 10.0.9); #29430 is CompareIdentifiers failing due to data changing between split queries.

Originally hit in production (EF Core 10.0.8, Microsoft.EntityFrameworkCore.SqlServer, SQL Server 2017), where the subquery source is a 20-branch UNION ALL "pivot" over tax-variant tables of Brazilian NF-e fiscal documents, correlated inside the items collection projection: single-query mode silently materialized ~10.4k collection elements where 13.8k were expected (every item beyond the first of each multi-item document was dropped), and split-query mode crashed with the exception above surfacing through PopulateSplitCollectionAsync → CompareIdentifiers. Our workaround was to stop using the keyless union source inside collection projections (falling back to LEFT JOINs over the keyed variant tables).

---

Disclosure: this bug was diagnosed, minimized and drafted with the assistance of an AI coding agent (Claude Code, Anthropic), supervised by the reporting developer, who reviewed the analysis and ran the repro before filing.

Your code

Runnable console app (.NET 10). Project file references only:

<PackageReference Include="Microsoft.EntityFrameworkCore.Sqlite" Version="10.0.9" />

Program.cs:

// Repro: correlated single-result subquery over a keyless UNION ALL source
// inside a collection projection breaks the collection's element identifier.
// - Single query: silently collapses the collection to one element per parent.
// - Split query: throws ArgumentOutOfRangeException in CompareIdentifiers.
using Microsoft.Data.Sqlite;
using Microsoft.EntityFrameworkCore;

using var connection = new SqliteConnection("DataSource=:memory:");
connection.Open();

var options = new DbContextOptionsBuilder<AppDbContext>()
    .UseSqlite(connection)
    .Options;

using (var ctx = new AppDbContext(options))
{
    ctx.Database.EnsureCreated();
    ctx.Add(new Order
    {
        Id = 1,
        Lines =
        [
            new OrderLine { OrderId = 1, LineNo = 1 },
            new OrderLine { OrderId = 1, LineNo = 2 },
        ]
    });
    ctx.Add(new TaxA { Id = 1, OrderId = 1, LineNo = 1, Kind = 10, Amount = 1.11m });
    ctx.Add(new TaxB { Id = 1, OrderId = 1, LineNo = 2, Kind = 20, Amount = 2.22m });
    ctx.SaveChanges();
}

// Expected in all cases: 1 order with 2 lines (LineNo 1 and 2).

Run("union source, single query", ctx => BuildQuery(ctx, unionSource: true).AsSingleQuery());
Run("union source, split query ", ctx => BuildQuery(ctx, unionSource: true).AsSplitQuery());
Run("keyed source, single query", ctx => BuildQuery(ctx, unionSource: false).AsSingleQuery());
Run("keyed source, split query ", ctx => BuildQuery(ctx, unionSource: false).AsSplitQuery());

void Run(string label, Func<AppDbContext, IQueryable<OrderDto>> query)
{
    using var ctx = new AppDbContext(options);
    try
    {
        var dto = query(ctx).Single();
        Console.WriteLine($"[{label}] lines materialized: {dto.Lines.Count} (expected 2)"
            + $" -> [{string.Join(", ", dto.Lines.Select(l => $"line {l.LineNo}: tax {l.Tax?.Kind}"))}]");
    }
    catch (Exception ex)
    {
        Console.WriteLine($"[{label}] threw {ex.GetType().Name}: {ex.Message}");
    }
}

static IQueryable<OrderDto> BuildQuery(AppDbContext ctx, bool unionSource)
{
    // Keyless source: UNION ALL of two keyed entity sets projected to an unmapped DTO —
    // a "pivot" unifying N variant tables into one shape. Replacing it with a single
    // keyed set (unionSource: false) makes every case work.
    var pivot = unionSource
        ? ctx.TaxesA
            .Select(t => new TaxRow { OrderId = t.OrderId, LineNo = t.LineNo, Kind = t.Kind, Amount = t.Amount })
            .Concat(ctx.TaxesB
                .Select(t => new TaxRow { OrderId = t.OrderId, LineNo = t.LineNo, Kind = t.Kind, Amount = t.Amount }))
        : ctx.TaxesA
            .Select(t => new TaxRow { OrderId = t.OrderId, LineNo = t.LineNo, Kind = t.Kind, Amount = t.Amount });

    // Correlated single-result subquery inside the collection projection.
    return ctx.Orders.Select(o => new OrderDto
    {
        Id = o.Id,
        Lines = o.Lines.Select(l => new LineDto
        {
            LineNo = l.LineNo,
            Tax = pivot
                .Where(r => r.OrderId == l.OrderId && r.LineNo == l.LineNo)
                .Select(r => new TaxDto { Kind = r.Kind, Amount = r.Amount })
                .FirstOrDefault()
        }).ToList()
    });
}

public class AppDbContext(DbContextOptions<AppDbContext> options) : DbContext(options)
{
    public DbSet<Order> Orders => Set<Order>();
    public DbSet<TaxA> TaxesA => Set<TaxA>();
    public DbSet<TaxB> TaxesB => Set<TaxB>();

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        modelBuilder.Entity<OrderLine>().HasKey(l => new { l.OrderId, l.LineNo });
        modelBuilder.Entity<Order>().HasMany(o => o.Lines).WithOne().HasForeignKey(l => l.OrderId);
    }
}

public class Order
{
    public int Id { get; set; }
    public List<OrderLine> Lines { get; set; } = [];
}

public class OrderLine
{
    public int OrderId { get; set; }
    public int LineNo { get; set; }
}

public class TaxA
{
    public int Id { get; set; }
    public int OrderId { get; set; }
    public int LineNo { get; set; }
    public int Kind { get; set; }
    public decimal Amount { get; set; }
}

public class TaxB
{
    public int Id { get; set; }
    public int OrderId { get; set; }
    public int LineNo { get; set; }
    public int Kind { get; set; }
    public decimal Amount { get; set; }
}

// Unmapped DTOs.
public class TaxRow
{
    public int OrderId { get; init; }
    public int LineNo { get; init; }
    public int Kind { get; init; }
    public decimal Amount { get; init; }
}

public class OrderDto
{
    public int Id { get; set; }
    public List<LineDto> Lines { get; set; } = [];
}

public class LineDto
{
    public int LineNo { get; set; }
    public TaxDto? Tax { get; set; }
}

public class TaxDto
{
    public int Kind { get; set; }
    public decimal Amount { get; set; }
}

Generated SQL for the failing single query (note the trailing ORDER BY — parent key only, no element columns):

SELECT "o"."Id", "s"."LineNo", "s"."Kind", "s"."Amount", "s"."c"
FROM "Orders" AS "o"
LEFT JOIN (
    SELECT "o0"."LineNo", "u1"."Kind", "u1"."Amount", "u1"."c", "o0"."OrderId"
    FROM "OrderLine" AS "o0"
    LEFT JOIN (
        SELECT "u0"."Kind", "u0"."Amount", "u0"."c", "u0"."OrderId", "u0"."LineNo"
        FROM (
            SELECT "u"."Kind", "u"."Amount", 1 AS "c", "u"."OrderId", "u"."LineNo",
                   ROW_NUMBER() OVER(PARTITION BY "u"."OrderId", "u"."LineNo" ORDER BY (SELECT 1)) AS "row"
            FROM (
                SELECT "t"."OrderId", "t"."LineNo", "t"."Kind", "t"."Amount"
                FROM "TaxesA" AS "t"
                UNION ALL
                SELECT "t0"."OrderId", "t0"."LineNo", "t0"."Kind", "t0"."Amount"
                FROM "TaxesB" AS "t0"
            ) AS "u"
        ) AS "u0"
        WHERE "u0"."row" <= 1
    ) AS "u1" ON "o0"."OrderId" = "u1"."OrderId" AND "o0"."LineNo" = "u1"."LineNo"
) AS "s" ON "o"."Id" = "s"."OrderId"
ORDER BY "o"."Id"

Stack traces

System.ArgumentOutOfRangeException: Index was out of range. Must be non-negative and less than the size of the collection. (Parameter 'index')
   at System.SZArrayHelper.get_Item[T](Int32 index)
   at lambda_method174(Closure, QueryContext, IExecutionStrategy, SplitQueryResultCoordinator)
   at Microsoft.EntityFrameworkCore.Query.Internal.SplitQueryingEnumerable`1.Enumerator.MoveNext()
   at System.Linq.Enumerable.TryGetSingle[TSource](IEnumerable`1 source, Boolean& found)
   at lambda_method171(Closure, QueryContext)
   at Microsoft.EntityFrameworkCore.Query.Internal.QueryCompiler.ExecuteCore[TResult](Expression query, Boolean async, CancellationToken cancellationToken)
   at Microsoft.EntityFrameworkCore.Query.Internal.QueryCompiler.Execute[TResult](Expression query)
   at Microsoft.EntityFrameworkCore.Query.Internal.EntityQueryProvider.Execute[TResult](Expression expression)
   at Program.<<Main>$>g__Run|0_4(String label, Func`2 query, <>c__DisplayClass0_0&) in .../Program.cs:line 45

(In production, with the SqlServer provider and async enumeration, the same exception surfaces through
Microsoft.EntityFrameworkCore.Query.Internal.SplitQueryingAsyncEnumerable`1 / PopulateSplitCollectionAsync -> CompareIdentifiers.)

Verbose output

No response

EF Core version

10.0.9 (repro; first observed on 10.0.8 in production)

Database provider

Microsoft.EntityFrameworkCore.Sqlite (repro); Microsoft.EntityFrameworkCore.SqlServer (original occurrence, SQL Server 2017)

Target framework

.NET 10.0

Operating system

Linux (Debian, repro); Windows 11 (original occurrence)

IDE

N/A (dotnet CLI)

[AndriySvyryd]

Bug confirmed

Reproduced on EF Core 11.0.0-preview.6 with identical output to the report. The reporter also confirmed it on 10.0.8/10.0.9 — not a recent regression.

Root cause (pinpointed)

In SelectExpression.AddJoin (~line 3311):

if (_identifier.Count == 0 || innerSelect._identifier.Count == 0)
{
    _identifier.Clear();  // ← incorrectly clears the outer's identifiers
    innerSelect._identifier.Clear();
}

When the inner side (pivot = UNION ALL/Concat) has empty identifiers, this clears the outer Lines collection's identifiers too — even though isToOneJoin: true is passed (the FirstOrDefault() cannot increase cardinality, so the outer's uniqueness is unaffected). This happens inside Lines.ApplyProjection(), after the identifier-count check at line 947 has already passed, so the guard never sees the corrupted state.

Fix direction

// Change:
if (_identifier.Count == 0 || innerSelect._identifier.Count == 0)
// To:
if (_identifier.Count == 0 || (innerSelect._identifier.Count == 0 && !isToOneJoin))

The isToOneJoin parameter is already in scope at that point.