Selecting the latest related entity generates ROW_NUMBER over unrelated rows despite parent filtering

Start with the documented LINQ reproduction and generated SQL in the issue, then trace EF Core's translation of ordered First over a related entity and compare it with the scalar projection and correlated Id workaround. The issue gives no source file, test, or entry point to start from, so locate existing query translation tests for this pattern and provider. Done means a selective parent filter avoids unnecessary work over unrelated child histories where appropriate, while preserving results and First or FirstOrDefault behavior.

Bug description

Selecting the latest related entity for one filtered parent generates a ROW_NUMBER() subquery over the entire child table. In the reproduction below, PostgreSQL reads 300,030 version rows to return one version. An equivalent parent-correlated query reads 30 version rows plus one primary-key lookup.

The example uses standard EF Core navigation properties and LINQ.

Data and intended operation

Consider documents with numbered versions. Every document belongs to a project. We need the latest version of each document in project 42.

Documents	ProjectId	Versions per document	Version rows
One requested document, Id 10001	42	30	30
10000 unrelated documents, Ids 1 through 10000	1	30	300000
Total			300030

Version numbers are unique within each document in this dataset. Each version contains a 128-character payload. EF creates an index on the foreign key Versions.DocumentId by convention. No composite ordering index or index on Documents.ProjectId is configured.

The requested document deliberately has the largest Id. This makes the broad ordered scan visible: a merge join cannot finish before reaching that document. Other key distributions and execution plans can read fewer rows.

LINQ that produces the broad window

var query = db.Documents.AsNoTracking()
    .Where(d => d.ProjectId == 42)
    .Select(d => new
    {
        d.Id,
        LatestVersion = d.Versions
            .OrderByDescending(v => v.Number)
            .First()
    });

The filter is already before the projection in LINQ. Moving that same Where earlier does not constrain the generated child subquery in this reproduction.

Actual SQL from ToQueryString():

SELECT d."Id", v1."Id", v1."DocumentId", v1."Number", v1."Payload"
FROM "Documents" AS d
LEFT JOIN (
    SELECT v0."Id", v0."DocumentId", v0."Number", v0."Payload"
    FROM (
        SELECT v."Id", v."DocumentId", v."Number", v."Payload", ROW_NUMBER() OVER(PARTITION BY v."DocumentId" ORDER BY v."Number" DESC) AS row
        FROM "Versions" AS v
    ) AS v0
    WHERE v0.row <= 1
) AS v1 ON d."Id" = v1."DocumentId"
WHERE d."ProjectId" = 42

Why a small result can require a large scan

The inner relation contains every row of Versions; it has neither the requested project filter nor a correlation to the currently selected document. It partitions versions by document, orders each partition, and keeps row 1. The outer query then joins those latest-version rows to the documents in project 42.

This SQL shape permits work proportional to unrelated version history. It does not mean all those rows are transferred to the application: the returned result still contains one document and one version. The cost is inside the database.

SQL describes the result, while PostgreSQL chooses the physical execution order. A window function alone does not prove a complete scan. In this particular execution, the actual plan confirms the broad scan:

Merge Right Join                             actual rows=1, loops=1
  WindowAgg                                  actual rows=10001, loops=1
    Run Condition: row_number() <= 1
    Incremental Sort                         actual rows=300030, loops=1
      Index Scan on Versions                 actual rows=300030, loops=1
        Index: IX_Versions_DocumentId
  Index Scan on Documents                    actual rows=1, loops=1
    Filter: ProjectId = 42
    Rows Removed by Filter: 10000

This is an abbreviated tree from the saved JSON plan. The window emits only 10,001 rows, but its input scan and sort consume 300,030. The row_number() <= 1 run condition does not eliminate that input work. An Index Scan can still visit all rows; the presence of an index is not evidence that the lookup is selective.

The parent table also needs filtering in this intentionally minimal schema. That is separate from reading the unrelated child histories and remains visible in the workaround plan.

Pure EF Core workaround

Select the latest version Id as a correlated scalar subquery, then select the entity matching that Id:

var query = db.Documents.AsNoTracking()
    .Where(d => d.ProjectId == 42)
    .Select(d => new
    {
        d.Id,
        LatestVersion = d.Versions.First(v =>
            v.Id == d.Versions
                .OrderByDescending(version => version.Number)
                .Select(version => version.Id)
                .First())
    });

Actual generated SQL:

SELECT d."Id", v1."Id", v1."DocumentId", v1."Number", v1."Payload"
FROM "Documents" AS d
LEFT JOIN LATERAL (
    SELECT v."Id", v."DocumentId", v."Number", v."Payload"
    FROM "Versions" AS v
    WHERE d."Id" = v."DocumentId" AND v."Id" = (
        SELECT v0."Id"
        FROM "Versions" AS v0
        WHERE d."Id" = v0."DocumentId"
        ORDER BY v0."Number" DESC
        LIMIT 1)
    LIMIT 1
) AS v1 ON TRUE
WHERE d."ProjectId" = 42

In the measured plan, PostgreSQL first selects the requested document, reads its 30 versions through the foreign-key index, sorts those 30 candidates to obtain the latest Id, and fetches that version through its primary key. No window function is generated.

The abbreviation below shows the relevant operations; the complete plan is included with the reproduction:

Nested Loop Left Join                        actual rows=1, loops=1
  Seq Scan on Documents                      actual rows=1, loops=1
    Filter: ProjectId = 42
    Rows Removed by Filter: 10000
  Limit                                      actual rows=1, loops=1
    InitPlan
      Limit                                  actual rows=1, loops=1
        Sort, top-N heapsort
          Index Scan on Versions             actual rows=30, loops=1
            Index: IX_Versions_DocumentId
    Index Scan on Versions                   actual rows=1, loops=1
      Index: PK_Versions

Measured comparison

Both LINQ queries were executed against the same database and returned the same version Id, with Number == 30. Plans were then collected with EXPLAIN (ANALYZE, BUFFERS, FORMAT JSON).

Observation	Ordered First entity projection	Correlated Id workaround
Rows returned	1	1
Version rows emitted by base scans	300030	30 plus 1
WindowAgg present	Yes	No
Execution time reported by EXPLAIN	123.053 ms	0.495 ms
Shared buffer hits at the root	6737	53

These are single local plan measurements, not a statistical benchmark or a guaranteed speedup. Both plans reported zero shared blocks read from disk; buffer hits count accesses, not distinct pages. The row counts demonstrate the avoidable work independently of the timing difference.

Scope and tradeoffs

The problematic shape is selecting a whole related entity. Selecting one scalar can translate differently. For example:

var query = db.Documents.AsNoTracking()
    .Where(d => d.ProjectId == 42)
    .Select(d => d.Versions
        .OrderByDescending(v => v.Number)
        .Select(v => v.Payload)
        .First());

Actual SQL for that scalar projection already uses a correlated LIMIT 1 and has no window:

SELECT (
    SELECT v."Payload"
    FROM "Versions" AS v
    WHERE d."Id" = v."DocumentId"
    ORDER BY v."Number" DESC
    LIMIT 1)
FROM "Documents" AS d
WHERE d."ProjectId" = 42

Consequently, replacing every ordered First mechanically can introduce an unnecessary extra lookup. The workaround above starts from the filtered parents. Applying a correlated top-Id predicate to every row of the whole child table is a different query shape and may repeat expensive work.

A window over children already restricted to the requested parent Ids is another distinct case. For example, Versions.Where(v => documentIds.Contains(v.DocumentId)).GroupBy(...) may generate a window whose input is already bounded. Inspect where the restriction lands in the actual SQL and execution plan before changing it.

The example guarantees at least one version per document. It verifies equivalent results for that invariant; it does not establish empty-navigation behavior. Preserve the application's required First or optional FirstOrDefault contract when adapting it. For nonunique version numbers, define a deterministic tie-breaker and use it consistently in both queries.

An index beginning with DocumentId and then the ordering column may help a correlated lookup avoid sorting. It does not by itself guarantee that a broad window stops reading unrelated rows. When many parents are requested, a set-based window may also be preferable to repeated correlated lookups.

Wide projections that independently repeat the latest-entity selection can produce multiple ranking subqueries, multiplying the child-side work. That larger shape is not required for this reproduction.

Expected behavior and question for maintainers

For a selective parent filter, can EF Core preserve the parent correlation when translating an ordered first-related-entity projection, or otherwise restrict the window input to the requested parents?

The correlated LATERAL ... LIMIT 1 translation above demonstrates a selective alternative for this provider and workload. The request is to avoid unnecessary work on unrelated child histories where appropriate, rather than to prohibit window functions.

EF Core documentation shows ROW_NUMBER translations for first-row-per-group queries, so using a window is an established translation strategy. Translation also depends on the LINQ shape and provider. See EF Core 6 GroupBy improvements and Complex Query Operators. Execution plans are needed to distinguish a SQL-shape concern from measured excess work; see EF Core performance diagnosis.

Your code

using System.Text.Json;
using Microsoft.EntityFrameworkCore;
using Npgsql;
using Testcontainers.PostgreSql;

await using var postgres = new PostgreSqlBuilder("postgres:16").Build();
await postgres.StartAsync();
var options = new DbContextOptionsBuilder<ReproContext>()
    .UseNpgsql(postgres.GetConnectionString()).Options;
await using var db = new ReproContext(options);
await db.Database.EnsureCreatedAsync();

// Arrange
var documents = Enumerable.Range(1, 10_001).Select(documentId => new Document
{
    Id = documentId,
    ProjectId = documentId == 10_001 ? 42 : 1,
    Versions = Enumerable.Range(1, 30).Select(number => new DocumentVersion
    {
        Id = (documentId - 1) * 30 + number,
        Number = number,
        Payload = new string('x', 128)
    }).ToList()
}).ToList();
db.AddRange(documents);
await db.SaveChangesAsync();
db.ChangeTracker.Clear();
await db.Database.ExecuteSqlRawAsync("ANALYZE");

// Act
var original = db.Documents.AsNoTracking().Where(d => d.ProjectId == 42)
    .Select(d => new
    {
        d.Id,
        LatestVersion = d.Versions.OrderByDescending(v => v.Number).First()
    });
var workaround = db.Documents.AsNoTracking().Where(d => d.ProjectId == 42)
    .Select(d => new
    {
        d.Id,
        LatestVersion = d.Versions.First(v =>
            v.Id == d.Versions.OrderByDescending(version => version.Number)
                .Select(version => version.Id).First())
    });
var scalar = db.Documents.AsNoTracking().Where(d => d.ProjectId == 42)
    .Select(d => d.Versions.OrderByDescending(v => v.Number)
        .Select(v => v.Payload).First());
var originalResult = await original.ToListAsync();
var workaroundResult = await workaround.ToListAsync();

// Assert
if (originalResult.Count != 1 || workaroundResult.Count != 1
    || originalResult[0].LatestVersion.Id != workaroundResult[0].LatestVersion.Id
    || originalResult[0].LatestVersion.Number != 30)
{
    throw new InvalidOperationException("The queries returned unexpected results.");
}

await using var connection = new NpgsqlConnection(postgres.GetConnectionString());
await connection.OpenAsync();
await using (var versionCommand = new NpgsqlCommand("SELECT version()", connection))
{
    Console.WriteLine(await versionCommand.ExecuteScalarAsync());
}

var queries = new Dictionary<string, string>
{
    ["original"] = original.ToQueryString(),
    ["workaround"] = workaround.ToQueryString(),
    ["scalar"] = scalar.ToQueryString()
};
foreach (var query in queries)
{
    await File.WriteAllTextAsync($"{query.Key}.sql", query.Value);
    await using var command = new NpgsqlCommand(
        "EXPLAIN (ANALYZE, BUFFERS, FORMAT JSON) " + query.Value, connection);
    var plan = (string)(await command.ExecuteScalarAsync())!;
    await File.WriteAllTextAsync($"{query.Key}.plan.json", plan);
    using var json = JsonDocument.Parse(plan);
    Console.WriteLine($"{query.Key}: {json.RootElement[0].GetProperty("Execution Time")} ms");
}

public sealed class Document
{
    public int Id { get; set; }
    public int ProjectId { get; set; }
    public List<DocumentVersion> Versions { get; set; } = [];
}

public sealed class DocumentVersion
{
    public int Id { get; set; }
    public int DocumentId { get; set; }
    public Document Document { get; set; } = null!;
    public int Number { get; set; }
    public string Payload { get; set; } = "";
}

public sealed class ReproContext : DbContext
{
    public ReproContext(DbContextOptions<ReproContext> options) : base(options) { }
    public DbSet<Document> Documents => Set<Document>();
    public DbSet<DocumentVersion> Versions => Set<DocumentVersion>();
}

Stack traces

Verbose output

EF Core version

10.0.12

Database provider

Npgsql.EntityFrameworkCore.PostgreSQL

Target framework

net10.0

Operating system

Debian, x86_64

IDE

No response