Where / Single / Count

Summary#

You query an entity by naming it and writing a predicate: Order.Where(o => o.Total > 100m). The predicate is compiled into the database query — it is not a filter applied to rows you already loaded — so a table with ten million rows costs you the ones you asked for.

Signature#

<Entity>.Where(<e> => <bool>)            // a set of rows — materialise with .ToList()
<Entity>.Single(<e> => <bool>)           // exactly one; faults if none, or if several
<Entity>.FirstOrDefault(<e> => <bool>)   // the first, or null
<Entity>.Count()                         // how many
<Entity>.Count(<e> => <bool>)            // how many match
<Entity>.Any(<e> => <bool>)              // is there at least one

Description#

Where, Single, Count, Any — which one?#

They differ in what they promise, and picking the wrong one is how a bug hides:

Single is a claim: there is exactly one. Use it when a second match would mean the data is broken — and be glad it faults, because a FirstOrDefault there would quietly pick one and let the corruption spread.

entity Order {
  [Required] string Code;
  decimal Total;
  bool Cancelled;
}

Order ByCode(string code) {
  return Order.Single(o => o.Code == code);        // a code identifies exactly one order
}

Order LatestOrNull(decimal min) {
  return Order.FirstOrDefault(o => o.Total >= min); // there may be none — and that is fine
}

int BigOrders(decimal min) {
  return Order.Count(o => o.Total >= min && !o.Cancelled);
}

bool AnyCancelled() {
  return Order.Any(o => o.Cancelled);
}

The predicate runs in the database#

Order.Where(o => o.Total > 100m) does not fetch every order and sift them. It becomes a WHERE clause. That is why you should express the filter in the predicate rather than fetching and testing in a loop:

// GOOD — the database returns the rows you want
decimal BigTotal(decimal min) {
  var total = 0m;
  foreach (var o in Order.Where(o => o.Total >= min).ToList()) {
    total += o.Total;
  }
  return total;
}

Fetching everything and filtering in a foreach gives the same answer on your laptop with fifty rows, and takes the application down when the table has five million.

Dates and arithmetic go in the predicate too#

A predicate is not limited to comparing columns to constants. Date arithmetic on a column — including a shift by another COLUMN's value — becomes part of the WHERE clause, and so does the current time:

entity Kiln {
  [Required, MaxLength(60)] string Name;
  [Required] int FireEveryDays;
  DateTime? LastFiredAt;
  security { allow read, create, update when IsAnonymous || IsAuthenticated; }
}

// Never fired, or fired longer ago than its own interval.
Kiln[] Due() {
  return Kiln.Where(p => p.LastFiredAt == null
                      || p.LastFiredAt.Value.AddDays(p.FireEveryDays) < DateTime.UtcNow).ToList();
}

The alternative — Kiln.ToList() and then a LINQ filter over the result — reads almost the same and is a different program: it fetches the whole table and does the work in memory. That is fine for the fifty rows you are testing with and is the shape that stops scaling first.

See also#

• ToList — turning a Where into rows you can walk
• Skip / Take (paging) — Skip / Take
• Distinct — removing duplicates
• relations — why children come from a collection, not a filtered query