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.

Values from outside the query#

A predicate can use anything your code already holds: a local, a parameter, an element of a list, a field of that element, a function's answer. Each is evaluated once, before the query runs, and sent to the database as a parameter — the same value you would get by putting it in a local first, which is what C# does too. The same holds in a page's query over a list of rows the page holds: Shot.Count(s => s.Bottle.Name == bottles[0].Name).

entity Order {
  [Required] string Code;
  decimal Total;
  security { allow read, create when IsAnonymous || IsAuthenticated; }
}

class Target { public string Code; public decimal AtLeast; }

int Matching(List<Target> targets) {
  var n = 0;
  foreach (var t in targets) {
    n += Order.Count(o => o.Code == t.Code && o.Total >= t.AtLeast);
  }
  return n;
}

Order? FirstTarget(List<Target> targets) {
  return Order.FirstOrDefault(o => o.Code == targets[0].Code);
}

int AboveTheLast(List<Order> recent) {
  return Order.Count(o => o.Total > recent.Last().Total);
}

What the predicate cannot do is pick from your list by the row — targets[o.Rank].Code, or targets.First(t => t.Code == o.Code).AtLeast is a different value for every row, and the database cannot index a list your code holds. osy validate refuses those by name (QUERY_NO_SQL_FORM). Either read the rows first (.ToList()) and do that part in memory, or turn the list into a set the query can test with Contains — for example var codes = targets.Select(t => t.Code).ToList(); and then Order.Where(o => codes.Contains(o.Code)).

A null in the predicate answers as C##

A predicate over stored rows means what the same lambda means over the loaded rows. Where(o => o.Status != "Closed") keeps an order whose status is null, because null != "Closed" is true; o.Region == region matches the orders with no region when region is null; and !(o.Total > 100) keeps an order with no total. SQL's own rule (a comparison with null is neither true nor false) would drop all three, so the platform writes the comparison the way C# answers it. A comparison of a column with a value that is never null is written plainly, so an index still serves it.

A security rule is the exception, on purpose: a read or write rule keeps SQL's rule, so allow read where Team == user.Team gives a principal with no team nothing rather than every row with no team.

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