EF 11 Drops a Redundant Join From Split Queries

EF 11 stops joining reference navigations in the collection statements of a split query and drops their keys from ORDER BY. The database can't remove those joins on its own, and EF 11 still keeps one for a required reference that isn't included first. In my PostgreSQL benchmark, a query with one reference got 7 to 11 percent faster, and one with four got 21 to 24 percent faster.

Back in September 2022, in the third (yes, 3rd) issue of this newsletter, I wrote about EF query splitting. I was fixing a problematic query with two levels of Include that kept timing out. Using AsSplitQuery fixed it.

A split query loads each included collection with its own SQL statement instead of one big JOIN. But each of those extra statements also carried a JOIN it didn't need, and the database can't optimize it away.

EF 11 fixes this, so I had to give it a try and measure if this gives a performance improvement. How much depends on the number of references, so I measured it with one and with four.

What a Split Query Sends

Here's the query from a small orders API I built for this test:

var orders = await db.Orders
    .Where(o => o.OrderedAt >= from && o.OrderedAt < to)
    .Include(o => o.Customer)
    .Include(o => o.Lines)
        .ThenInclude(l => l.Product)
    .Include(o => o.Payments)
    .AsSplitQuery()
    .AsNoTracking()
    .ToListAsync();

Customer is a reference navigation, one per order, and Lines and Payments are collections. In a single query, the two collections multiply each other, so an order with five lines and two payments comes back as ten rows.

AsSplitQuery sends one SELECT for the orders and one more for each collection. The tradeoff: three round trips instead of one, and nothing keeps the statements consistent unless you wrap them in a transaction.

The Join EF 11 Removes

Here's the statement that loads the lines, as EF 10 generates it for PostgreSQL:

SELECT s."Id", s."OrderId", s."ProductId", s."Quantity", ..., o."Id", c."Id"
FROM "Orders" AS o
INNER JOIN "Customers" AS c ON o."CustomerId" = c."Id"
INNER JOIN (
    SELECT o0."Id", o0."OrderId", ..., p."Name", p."Price", p."Sku"
    FROM "OrderLines" AS o0
    INNER JOIN "Products" AS p ON o0."ProductId" = p."Id"
) AS s ON o."Id" = s."OrderId"
WHERE o."OrderedAt" >= @from AND o."OrderedAt" < @to
ORDER BY o."Id", c."Id"

And the same statement from EF 11:

SELECT s."Id", s."OrderId", s."ProductId", s."Quantity", ..., o."Id"
FROM "Orders" AS o
INNER JOIN (
    SELECT o0."Id", o0."OrderId", ..., p."Name", p."Price", p."Sku"
    FROM "OrderLines" AS o0
    INNER JOIN "Products" AS p ON o0."ProductId" = p."Id"
) AS s ON o."Id" = s."OrderId"
WHERE o."OrderedAt" >= @from AND o."OrderedAt" < @to
ORDER BY o."Id"

In EF 10, the Customers join on line 3 only feeds c."Id" into the ORDER BY. EF 10 adds it so the rows come back in the same order as the first statement, which does need the customer's columns. But an order has exactly one customer, so sorting by c."Id" after o."Id" doesn't change anything.

The EF docs have listed this as a drawback of split queries for years, and the issue closed in March 2026.

EF 11 drops the join and c."Id":

The payments statement loses the same join. The Products join stays, since each line needs its product.

Why the Database Can't Remove It

Query planners rewrite a lot of what you send them, and removing a join is one of those rewrites. But only when the query uses nothing from the joined table. EF 10 selects c."Id" and sorts by it, so the join runs.

Here are the relevant lines from PostgreSQL 18's plan for the EF 10 statement:

Sort Key: o0."OrderId", c."Id"
->  Hash Join  (actual time=0.746..8.686 rows=9865.00 loops=1)
      Hash Cond: (o."CustomerId" = c."Id")
      ->  Seq Scan on "Customers" c  (actual time=0.005..0.040 rows=1000.00 loops=1)

The EF 11 plan doesn't have them, and the median of ten warm runs went from 12.13 ms to 10.40 ms. SQL Server 2022 keeps the join too.

Performance Improvements

I measured both versions with BenchmarkDotNet. Here's the benchmark for the two queries from the API, with a pooled DbContext:

[MemoryDiagnoser]
public class SplitQueryBenchmarks
{
    private const string ConnectionString =
        "Host=127.0.0.1;Port=5438;Database=orders;Username=mnw220;Password=mnw220";

    private static readonly DateTime From = Seeder.FirstDay.AddDays(10);
    private static readonly DateTime To = From.AddDays(3);

    private PooledDbContextFactory<AppDbContext> _factory = null!;

    [GlobalSetup]
    public void Setup() =>
        _factory = new PooledDbContextFactory<AppDbContext>(
            new DbContextOptionsBuilder<AppDbContext>().UseNpgsql(ConnectionString).Options);

    [Benchmark]
    public async Task<int> Page()
    {
        await using var db = _factory.CreateDbContext();
        var orders = await OrderQueries.PageAsync(db, customerId: 42, page: 0, CancellationToken.None);
        return orders.Count;
    }

    [Benchmark]
    public async Task<int> Report()
    {
        await using var db = _factory.CreateDbContext();
        var orders = await OrderQueries.RangeAsync(db, From, To, CancellationToken.None);
        return orders.Count;
    }
}

One benchmark project targets .NET 10 with EF 10 and .NET 11 with EF 11, and runs both queries against the same Postgres database:

var config = DefaultConfig.Instance
    .AddJob(Job.Default.WithRuntime(CoreRuntime.Core10_0).WithId("EF 10").AsBaseline())
    .AddJob(Job.Default.WithRuntime(CoreRuntime.Core11_0).WithId("EF 11"));

BenchmarkRunner.Run<SplitQueryBenchmarks>(config, args);

The project file references a different EF package for each target framework, so each job builds against its own EF version. Page loads one customer's 20 latest orders, and Report loads about 2,000 orders from a three-day window.

One reference is the smallest case, because EF 10 repeats every reference join in every collection statement. So I also ran both queries with four references on the order: Customer, a required ShippingAddress, an optional BillingAddress, and an optional Carrier.

One reference was worth 7 to 11 percent, and four were worth 21 to 24 percent. Each EF 11 job also runs on .NET 11, so runtime changes are part of these numbers.

The split query lab has the benchmark project, the SQL from both versions, and my results.

The EF team's own benchmark measured 16 to 29 percent on SQL Server, for split queries with one to five optional references.

The Join EF 11 Still Keeps

EF 11 didn't remove all four joins. Here's its payments statement for the four-reference report:

SELECT p0."Id", p0."Amount", p0."Method", p0."OrderId", p0."PaidAt", o."Id"
FROM "Orders" AS o
INNER JOIN "Addresses" AS a ON o."ShippingAddressId" = a."Id"
INNER JOIN "Payments" AS p0 ON o."Id" = p0."OrderId"
WHERE o."OrderedAt" >= @from AND o."OrderedAt" < @to
ORDER BY o."Id"

The Customers, billing address, and Carriers joins are gone, but the ShippingAddress join is still there.

In EF 11, optional references (LEFT JOIN) are always removed. A required reference (INNER JOIN) is removed only when it comes first in the Include chain.

Here Customer comes first, so its join is gone. With ShippingAddress first, EF 11 removed that join and kept the Customers join.

So include the required reference with the biggest table first.

Summary

EF 11 removes a JOIN that split queries have carried since EF 5, and you don't change any code to see the benefits. The database couldn't remove it for you.

On PostgreSQL that was worth 7 to 11 percent with one reference, and 21 to 24 percent with four.

Whether or not those benefits materialize to anything substantial in your applications is something you'll have to measure.

I still recommend upgrading to .NET 11 to get all the runtime improvements. The new EF 11 features come as a nice bonus.

Thanks for reading.

And stay awesome!