# Where / Single / Count

> Query an entity by writing a predicate over it. The query runs in the database — not a filter over rows you already fetched — so a table with millions of rows costs you only the ones you ask for.

<!-- id: query-where · area: query · stability: stable · html: https://osysharp.com/reference/query/where/ -->

## Summary        {#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      {#signature}
```osy syntax
<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    {#description}

### `Where`, `Single`, `Count`, `Any` — which one?   {#choosing}
They differ in what they promise, and picking the wrong one is how a bug hides:

| Call | Returns | When there is no match | When there are several |
|---|---|---|---|
| `Where` | a set (materialise with [`.ToList()`](https://osysharp.com/reference/query/tolist/)) | an empty set | all of them |
| `Single` | one row | **faults** | **faults** |
| `FirstOrDefault` | one row or `null` | `null` | the first |
| `Count` | a number | `0` | the count |
| `Any` | a bool | `false` | `true` |

`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.

```osy title="each one, doing its job" test app=query-where
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   {#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:

```osy title="filter in the query, not in the loop" test app=query-where
// 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   {#dates}
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:

```osy title="a due-date predicate, evaluated in the database" test app=query-where-dates
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   {#captured-values}
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)`.

```osy title="a held list's elements and their fields, inside the predicate" test app=query-where-captured
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#   {#nulls}
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       {#see-also}
- [ToList](https://osysharp.com/reference/query/tolist/) — turning a `Where` into rows you can walk
- [Skip / Take (paging)](https://osysharp.com/reference/query/paging/) — `Skip` / `Take`
- [Distinct](https://osysharp.com/reference/query/distinct/) — removing duplicates
- [relations](https://osysharp.com/reference/entity/relations/) — why children come from a collection, not a filtered query