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 row at position n (0-based); outside → ArgumentOutOfRangeException / default

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 row at position n, counting from 0, and n is any int — a constant, a parameter, a computed value. It composes with Skip (Skip(2).ElementAt(3) is the sixth row), and it still reads one row: the position becomes the read's OFFSET, with LIMIT 1. (Except after a Take, or for an index a function CALL computes — C# evaluates it once, and it may have effects — where the rows are read and indexed in memory. Put the call's answer in a local first and the read is one row again.)

A position outside the sequence — past the end, or negative — is C#'s ArgumentOutOfRangeException, as it is for a list, and ElementAtOrDefault(n) answers null (or a value type's zero) instead:

string NthCode(int n) {
  try {
    return Order.OrderBy(o => o.PlacedAt).ElementAt(n).Code;
  } catch (ArgumentOutOfRangeException) {
    return "no such position";     // n < 0, or n >= the number of orders
  }
}

string NthCodeOrNone(int n) {
  return Order.OrderBy(o => o.PlacedAt).ElementAtOrDefault(n)?.Code ?? "none";
}

The same two verbs work on a list or an array you already hold, with the same answers — xs.ElementAt(i) throws where xs[i] throws.

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.

What a fault is — C#'s, and where a 404 comes from#

The fault is C#'s InvalidOperationException, over a query and over a list, on the server and in the browser, and it opens with .NET's own sentence — Sequence contains no elements, Sequence contains no matching element, Sequence contains more than one (matching) element — followed by where it happened and how many rows the set held. So catch (InvalidOperationException) takes it, exactly as it would in C#.

It is not NotFoundException, and a caller over HTTP is not answered 404 for it: Single() on nothing is the program's own claim failing, not the caller asking for something absent. When "absent" is a real answer, say so:

var order = Order.SingleOrDefault(o => o.Code == code)
    ?? throw new NotFoundException($"No order {code}.");   // → 404 over HTTP, catchable as NotFoundException

A page about one row needs neither: take the row as its route parameter and the platform answers the app's not-found page for an id no row has ([[ui-routing#row-parameter]]).

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  → InvalidOperationException ("Sequence contains no matching element"), as in C#
  // two rows → InvalidOperationException ("…more than one matching element"): 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) — Skip/Take, for a page of rows rather than one