Many-to-many collection `ORDER BY` loses its only discriminating column (regression in 11.0)

[ChrisJollyAU]

Bug description

Summary

For a skip navigation traversed from the principal side (EntityOne.TwoSkip), the ORDER BY EF emits for a collection Include no longer constrains the order of the collection elements at all. On EF 10 it did.

The result is that many-to-many collection contents come back in whatever order the provider's access path happens to produce. In-box providers are unaffected in practice because their physical row order coincides with key order, but the generated SQL no longer requests any ordering, and providers where those two diverge now fail.

I see 288 test failures in EFCore.Jet on 11.0 that were green on 10.0 — 164 in ManyToManyTrackingTestBase, 124 in ManyToManyLoadTestBase. Below is a reproduction on SQLite, so this can be confirmed without reference to my provider.

Cause

Two individually-sound optimizations that interact:

• Remove last ORDER BY for collection joins #24360 (5bcdd2e9eb, Aug 2021) — SelectExpression.cs:1086, "We omit the last ordering as an optimization". Drops the final identifier column from a collection's ORDER BY.
• Better SQL for to-one joins #37819 (be6a9208b0, Mar 2026) — SelectExpression.cs:3253. Stops adding to-one-joined entity keys to _identifier.

JoinOneToTwo's key is (OneId, TwoId). Before #37819 the identifier list was (OneId, TwoId, Id) and the omitted column was [s].[Id] — redundant, since [s].[TwoId] was already ordered and equal to it by the join predicate. After #37819 the list is (OneId, TwoId) and the omission takes [s].[TwoId] — the only discriminating column, because [s].[OneId] is constant under the WHERE.

Visible in #37819's own baseline diff (ManyToManyQuerySqlServerTest.cs):

-ORDER BY [e].[Id], [s].[OneId], [s].[TwoId], [s].[Id]
+ORDER BY [e].[Id], [s].[OneId]

The direction-dependence suggests this is incidental rather than intended: EntityTwo.OneSkip still orders correctly, because there the varying column (OneId) is first in the join key and so survives the omission. Only the principal-side direction loses its discriminator.

Reproduction (SQLite, in-box)

The order SQLite returns is physical row order — its plan for this shape is MATERIALIZE s | SCAN j | ..., a full scan of the join table, so rowid order falls straight through. The fixture passes only because SaveChanges sorts the seed inserts by key, making physical order and key order identical.

Perturb the physical order to match what any application that inserted relationships over time would have. In test/EFCore.Sqlite.FunctionalTests/ManyToManyLoadSqliteTestBase.cs, add to ManyToManyLoadSqliteFixtureBase:

protected override async Task SeedAsync(ManyToManyContext context)
{
    await base.SeedAsync(context);

    // Simulate an application that did not insert its relationships in key order.
    await context.Database.ExecuteSqlRawAsync("DELETE FROM JoinOneToTwo WHERE OneId = 3");
    foreach (var twoId in new[] { 10, 1, 19, 4, 16, 7, 13 })
    {
        await context.Database.ExecuteSqlRawAsync(
            $"INSERT INTO JoinOneToTwo (OneId, TwoId, JoinOneToTwoExtraId) VALUES (3, {twoId}, NULL)");
    }
}

Delete ManyToManyLoadTest.db, then run ManyToManyLoadSqliteTest.Load_collection_using_Query_already_loaded*:

total: 96   failed: 24   succeeded: 72

Assert.Equal() Failure: Collections differ (pos 0)
  at ManyToManyLoadTestBase.Load_collection_using_Query_already_loaded
     ManyToManyLoadTestBase.cs:317

No query hints, no scale, 7 rows. Reverting the fixture returns it to 96/96.

To confirm the ordering is purely physical rather than anything SQLite guarantees — same data, same rows, two semantically identical queries:

EF's actual Include shape (subquery)
   order : 10,1,19,4,16,7,13          <- physical order
   plan  : MATERIALIZE s | SCAN j | SEARCH e0 ... | SCAN s LEFT-JOIN

semantically identical, flattened
   order : 1,4,7,10,13,16,19          <- key order
   plan  : SEARCH e ... | SEARCH j USING COVERING INDEX sqlite_autoindex_JoinOneToTwo_1 (OneId=?)

The subquery EF wraps around the join blocks flattening, which selects the physical-order plan. So which order SQLite users get is decided by the shape of EF's generated SQL.

Why CI doesn't catch this

Three independent things have to coincide, and in the test fixture all three do:

1. SaveChanges sorts seed inserts by key, so physical row order equals key order.
2. EntityTwo.Id and JoinOneToTwo.TwoId are equal by the join predicate, so any path ordered by either table's key yields the same sequence.
3. On SQL Server the clustered PK on (OneId, TwoId) makes physical order be key order regardless of insert order.

Every access path therefore agrees, and no plan any in-box provider selects can produce a different answer. Green CI here is not evidence that the assertion holds — it's evidence that the fixture is written in the one order that makes it hold.

(Note on SQL Server: I could not reproduce this there at all. Its preferred plan is order-preserving at every scale tested; only a forced hash join starved of memory — OPTION (HASH JOIN, MAX_GRANT_PERCENT = 0) at ~20k+ rows — reorders. That's a plan the optimizer won't choose here, so I'm not offering it as evidence, but it does mean SQL Server can never surface this regardless of what the SQL says.)

Two failure shapes

1. Explicit order comparison. ManyToManyLoadTestBase.cs:317 compares the order of two different queries — the Include that populated the collection, and the loader query from collectionEntry.Query(). The loader query keeps [s].[TwoId] (its extra NotQuiteInclude level pushes the omission down a level); the plain Include does not.

2. Hidden order dependency — the more concerning one. Can_insert_many_to_many fails with Assert.Equal() Failure: Expected: 3, Actual: 1 at ManyToManyTrackingTestBase.cs:3418:

Assert.Equal(3, rightEntities[0].OneSkip.Count);

There is no ordering assertion here. The dependency runs through line 3402:

var rightEntities = context.ChangeTracker.Entries<EntityTwo>()
    .Select(e => e.Entity).OrderBy(e => e.Name).ToList();

Name is never set on these entities — 0 of the 216 CreateInstance calls in that file set it — so OrderBy is a stable sort over an all-null key, i.e. a no-op that resolves to change-tracker order, which is materialization order, which is row order. Whichever EntityTwo materialises first becomes rightEntities[0]. Expected 7721 (OneSkip.Count == 3); any other gives 1.

There are 107 OrderBy(e => e.Name) sites in ManyToManyTrackingTestBase.cs. This is why the failure count is what it is, and why "make the assertions order-insensitive" is a much larger job than it first appears — most of these don't read as order-dependent at all.

The near-identical test that passes

Load_collection_using_Query (ManyToManyLoadTestBase.cs:107) is structurally almost the same as Load_collection_using_Query_already_loaded (line 273) and passes everywhere, including on Jet — both with and without the workaround below. The difference is instructive, because it's a discriminating prediction rather than just an observation.

The omission at SelectExpression.cs:1086 applies to the innermost collection level, and that alone determines which queries are affected.

A loader query (collectionEntry.Query()) has two collection levels, because ManyToManyLoader appends a NotQuiteInclude of the inverse navigation (ManyToManyLoader.cs:262). The omission therefore lands on the inner [s0] level, and the EntityTwo level keeps its discriminator:

ORDER BY [e].[Id], [s].[OneId], [s].[TwoId], [s0].[OneId]    <- ManyToManyLoadSqlServerTest.cs:36

A plain Include has only one level, so the omission lands on EntityTwo itself:

-ORDER BY [e].[Id], [s].[OneId], [s].[TwoId], [s].[Id]
+ORDER BY [e].[Id], [s].[OneId]

Load_collection_using_Query only ever issues the loader query, and populates the navigation from it through fixup (line 122). Its EntityTwo ordering is therefore intact, and there is no second result set for it to disagree with — protected twice over. #37819 did change its SQL, but only at the [s0] level, whose ordering nothing observes.

Load_collection_using_Query_already_loaded populates the navigation from a plain Include (line 277, EntityTwo unordered) and compares it against the loader query (line 293, EntityTwo ordered). That mismatch is the failure.

The same one-level rule accounts for failure shape 2: Can_insert_many_to_many verifies through a plain Include, so its EntityTwo rows are unordered and materialization order decides which entity lands in rightEntities[0].

Impact

Beyond the tests: on SQLite, collection order for a many-to-many Include is physical row order. Any application whose relationship rows weren't inserted in key order — i.e. anything that accumulated them over time — now gets arbitrary collection order where 10.0 gave deterministic order. No test can observe this, because seeding is the one path where EF sorts the inserts itself.

Suggested fix

Skip the omission at SelectExpression.cs:1086 when the surviving ordering terms don't actually discriminate — rather than unconditionally dropping the last identifier column. That completes #37819 rather than reverting it, and preserves the optimization in the cases it was written for (where the dropped column really is redundant).

Workaround (validated)

Re-adding the single omitted term in a provider QueryTranslationPostprocessor gets EFCore.Jet to pass all those tests again
:

// after base.Process(query, ...)
var identifiers = GetIdentifiers(query);

if (identifiers.Count > 0
    && query is ShapedQueryExpression { QueryExpression: SelectExpression selectExpression }
    && !selectExpression.Orderings.Any(o => o.Expression.Equals(identifiers[^1].Column))
    && selectExpression.Orderings.Any())
{
    selectExpression.AppendOrdering(new OrderingExpression(identifiers[^1].Column, ascending: true));
}

That one term accounts for all 288 failures, which I think confirms the diagnosis.

Environment

• EF Core 11.0 (main), compared against 10.0
• Reproduced on SQLite (in-box) and EFCore.Jet
• Not reproducible on SQL Server — see note above

Your code

See details in description

Stack traces

Verbose output

EF Core version

11

Database provider

No response

Target framework

No response

Operating system

No response

IDE

No response