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

## 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