Bits of .NET

🔧 Same Query, Same Data – a Different Row Order Every Time You Run It

A LINQ query against Entity Framework returns the exact same set of rows on every run, just shuffled into a different order each time – no ORDER BY was written on purpose, but the assumption was that the database would at least be CONSISTENT about the order it left rows in.

🐞 The Problem

var orders = await db.Orders
    .Where(o => o.CustomerId == customerId)
    .ToListAsync();

// Run 1: returns rows in order [102, 105, 101, 103]
// Run 2, same data, same query: [105, 101, 103, 102]
// - a UI list built on this "just moves around" for no
//   apparent reason between page loads.

🔍 Why This Happens

SQL, and by extension Entity Framework, makes NO GUARANTEE about
row order unless an explicit ORDER BY is specified - the relational
model is fundamentally a set of rows, not a sequence. Without an
ORDER BY, the database is free to return rows in whatever order is
most convenient internally - influenced by index usage, physical
storage layout, parallel execution plans, or even just which CPU
core happened to scan which data page first. It can appear stable
for a long time purely by coincidence, then change the moment an
index changes or the query plan is recompiled.

✅ The Fix: Always Add an Explicit OrderBy for Anything User-Visible

• Add .OrderBy(o => o.Id) (or whatever column defines the order you actually want) to every query whose result order matters to a human or to downstream logic – never rely on implicit database ordering, even if it ‘seems to always work.’
• For paginated results specifically, an explicit, stable ORDER BY isn’t just cosmetic – without one, using .Skip()/.Take() across separate requests can return duplicate OR missing rows if the underlying implicit order shifts between calls.
• If a specific column doesn’t guarantee a unique order (ties on the same value), add a secondary tiebreaker column (like Id) to the OrderBy so the order is fully deterministic, not just ‘usually deterministic.’

📦 Correct Version

var orders = await db.Orders
    .Where(o => o.CustomerId == customerId)
    .OrderBy(o => o.CreatedDate)
    .ThenBy(o => o.Id) // tiebreaker for a fully deterministic order
    .ToListAsync();