First / Single / Last / ElementAt

Summary#

These return one row, and the one you pick is an assertion about your data:

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

Order ByCode(string code) {
  return Order.Single(o => o.Code == code);        // Code is unique — say so, and be told if it is ever not
}

Order? MaybeByCode(string code) {
  return Order.SingleOrDefault(o => o.Code == code);   // …and it may legitimately not exist
}

Order? Biggest() {
  return Order.OrderByDescending(o => o.Total).FirstOrDefault();   // "the biggest" — of however many
}

Signature#

<Query>.First()   / .First(o => p)             // the first row; FAULTS if there is none
<Query>.FirstOrDefault() / .FirstOrDefault(o => p)   // …or null

<Query>.Single()  / .Single(o => p)            // exactly one; FAULTS on none AND on several
<Query>.SingleOrDefault() / .SingleOrDefault(o => p) // …null on none; still faults on several

<Query>.Last()    / .LastOrDefault()           // requires an OrderBy
<Query>.ElementAt(n) / .ElementAtOrDefault(n)  // the n-th row; n must be a constant

Description#

First, Single, or the …OrDefault forms?#

They are not interchangeable, and the difference is what each one claims:

Reach for Single when the data says one. Looking up by a [Unique] code, or by id: if two ever came back, your data is broken and you want to know immediately — at the query, with a clear fault, rather than three screens later when the wrong one turns out to have been picked. First in that position would silently choose one and carry on, and that is the bug that takes a day to find.

Reach for First when several is normal and you want the top one — which almost always means you have said what "top" means with an OrderBy. First() on an unordered query returns an arbitrary row, and the database is free to pick a different one tomorrow.

Last needs an order#

Last() and LastOrDefault() require an OrderBy — the compiler refuses them without one. There is no "last" row in a set; there is only a last row in a sequence, and a sequence is what an OrderBy makes. The engine inverts your ordering and takes one row, so it costs the same as First().

Order? MostRecent() {
  return Order.OrderBy(o => o.PlacedAt).LastOrDefault();   // …or OrderByDescending + FirstOrDefault. Same query.
}

How do I take the n-th row? — ElementAt#

ElementAt(n) takes the n-th row (0-based) and needs a compile-time constant index — it becomes an OFFSET, and it composes additively with Skip. For a runtime offset, that is what Skip/Take is for.

Using one inside another query#

A single-row read can be written inline where another query needs its value, and it means what the two-line version means:

entity Invoice {
  [Required, MaxLength(40), Unique] string Number;
  [ForeignKey(Invoice)] InvoiceLine[] Lines;
}

entity InvoiceLine {
  [Required] Invoice Invoice;
  decimal Amount;
}

int LinesInline(string number) {
  return Invoice.Single(i => i.Number == number).Lines.Count();
}

int LinesHoisted(string number) {
  var invoice = Invoice.Single(i => i.Number == number);
  return invoice.Lines.Count();        // …what the line above is shorthand for
}

The inner read runs first and its result is used by the outer one — so Single's promise still holds where you wrote it (two matching invoices faults there, at the lookup, not somewhere downstream). Writing the local yourself is still the clearer choice when you need the row again, or when the name says something.

This works where the read is a value the outer query needs — navigating into its collection, as above, or comparing against it (line.Invoice == Invoice.Single(…)). Reading a FIELD straight off it (Invoice.Single(…).Number) still wants the local.

And a read that names the row being filtered is a different read for every candidate row, so there is nothing to run once. The compiler says so rather than guessing; reach the row you want through the reference or its collection instead.

None of them fetch more than they need#

Each of these becomes LIMIT 1 (Single asks for two, so it can tell you when there are two). You are not fetching a set and taking its head — the database returns one row.

You can Include on any of them, which is exactly what a detail page wants: one parent, its children already in hand.

Examples#

The three failure modes, said out loud:

Order Required(string code) {
  return Order.Single(o => o.Code == code);
  // no row  → faults (NotFound): the caller asked for a code that does not exist
  // two rows → faults (Conflict): `Code` is [Unique], so this is a broken database, not a lookup miss
}

Order? Optional(string code) {
  return Order.SingleOrDefault(o => o.Code == code);
  // no row  → null       (a legitimate "not found" — the caller decides what that means)
  // two rows → still faults (at most one is still an assertion, and it has been violated)
}

Order? Top() {
  return Order.OrderByDescending(o => o.Total).FirstOrDefault();
  // no row  → null; several → the biggest. Neither is an error: "several" is the normal case here.
}

See also#

• Where / Single / Count — the predicate these take, and Count/Any
• OrderBy / ThenBy — what Last() requires, and why First() without it is a coin toss
• Include (pre-loading relations) — one row, with its relations pre-loaded
• Skip / Take (paging) — a runtime offset, which is what ElementAt is not