OrderBy / ThenBy

Summary#

Sort a query with OrderBy, and add further keys with ThenBy:

entity Order {
  [Required, MaxLength(40)] string Code;
  decimal Total;
  DateTime PlacedAt;
}

List<Order> Newest(int howMany) {
  return Order.OrderByDescending(o => o.PlacedAt)
              .ThenBy(o => o.Code)          // a stable tiebreak — see below
              .Take(howMany)
              .ToList();
}

It becomes SQL ORDER BY. The database sorts; you do not fetch rows and sort them yourself.

Signature#

<Query>.OrderBy(o => key)              // ascending
<Query>.OrderByDescending(o => key)    // descending
<Query>.ThenBy(o => key)               // a further key
<Query>.ThenByDescending(o => key)     // a further key, descending

Each takes a key-selector lambda with one parameter, and nothing else — there is no comparer overload and no argument-less form. Any number of keys may accumulate.

Description#

The keys accumulate — a chained OrderBy does not re-sort#

This is the one deliberate divergence from C#, and it is worth knowing before it surprises you:

Order.OrderBy(o => o.Region).OrderBy(o => o.Total)     // sorts by (Region, Total)   ← NOT C# semantics
Order.OrderBy(o => o.Region).ThenBy(o => o.Total)      // sorts by (Region, Total)   ← the same thing

In C#, the second OrderBy would replace the sort — the result would be ordered by Total alone. Here the keys build one composite sort, so the two lines above are identical. ThenBy is simply the spelling that says what is happening, and it is the one to write.

Sort before you page#

Without an ORDER BY, a database may return rows in any order it likes — and it may return them in a different order for page 2 than it did for page 1. A paged query with no sort silently duplicates and drops rows.

List<Order> Page(int page, int size) {
  return Order.OrderByDescending(o => o.PlacedAt)
              .ThenBy(o => o.Code)          // the tiebreak is what makes the page STABLE
              .Skip(page * size)
              .Take(size)
              .ToList();
}

And note the tiebreak. Sorting by a key with duplicates (many orders placed the same second) leaves their relative order undefined, so a row can appear on two pages or on none. Add a unique final key — the Code above — and the sort is total, so the pages partition the rows exactly. See Skip / Take (paging).

Last needs an OrderBy#

Last() / LastOrDefault() require an OrderBy — "the last row" has no meaning without an order, and the database will not guess one for you. The engine inverts your keys and takes one row, so it is as cheap as First(). See First / Single / Last / ElementAt.

Where it may appear#

OrderBy composes with Where, Skip/Take, Include and the terminals. Two restrictions are worth knowing, both of which the compiler enforces:

• Before a Select projection, only OrderBy/OrderByDescending are accepted — ThenBy is not. Sort the projected result instead, or project into a class and sort that.
• Before a GroupBy, nothing but Where is accepted. Sorting the rows that are about to be collapsed into groups would not mean anything; sort the groups after the projection, which is supported.

Examples#

Sorting by something computed, and sorting the result of a grouping (which is where you usually want it — the top N by an aggregate):

class RegionTotal { public string Region; public decimal Total; }

entity Sale {
  [Required, MaxLength(60)] string Region;
  decimal Amount;
}

List<RegionTotal> TopRegions() {
  return Sale.GroupBy(s => s.Region)
             .Select(g => new RegionTotal { Region = g.Key, Total = g.Sum(s => s.Amount) })
             .OrderByDescending(r => r.Total)   // sort the GROUPS, by their aggregate
             .Take(5)
             .ToList();
}

See also#

• Skip / Take (paging) — Skip/Take, and why an unsorted page is a bug
• First / Single / Last / ElementAt — Last() and the ordering it requires
• GroupBy (and HAVING) — sorting groups by an aggregate
• Querying data — the shape of a query chain