# Querying data

> How you read data in Osy#. You write C# LINQ; it becomes one SQL statement. The rules that follow from that are the whole model: the predicate runs in the database (not over rows you fetched), and a verb that cannot become SQL is refused rather than run quietly over everything.

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

## Summary        {#summary}
You query data by writing **C# LINQ over your entities**. There is no query language to learn, no repository to
write, and no mapping layer to configure:

```osy title="a query, and what it is" test app=query-index
entity Customer {
  [Required, MaxLength(80)] string Name;

  security { allow create, read, update when IsAuthenticated || IsAnonymous; }
}

entity Order {
  [Required, MaxLength(40)] string Code;
  decimal Total;
  bool Cancelled;
  Customer? Customer;

  security { allow create, read, update when IsAuthenticated || IsAnonymous; }   // an entity with no security block is denied to everyone
}

List<Order> BigOpenOrders(decimal floor) {
  return Order.Where(o => o.Total > floor && !o.Cancelled)
              .OrderByDescending(o => o.Total)
              .Take(20)
              .ToList();
}
```

That is **one SQL statement**. Two things follow from it, and together they are the whole mental model:

1. **The predicate runs in the database.** It is not a filter over rows you already fetched. A table with ten million
   rows costs you the twenty you asked for.
2. **A verb that cannot become SQL is refused**, loudly, at compile time — never run quietly over the whole table.

## Description    {#description}

### 1. Three things you can query   {#sources}
The same verbs work over three different sources, and knowing which one you are on tells you what it costs:

| Source | What it is | Cost |
|---|---|---|
| **An entity** — `Order.Where(…)` | the table | SQL. You pay for the rows you asked for. |
| **A collection** — `order.Lines.Where(…)` | a parent's children ([Child collections (navigating a relation)](https://osysharp.com/reference/query/collections/)) | SQL, correlated to the parent. |
| **A local list** — `items.Where(…)` | a `List<T>` you built in code ([LINQ over a local list](https://osysharp.com/reference/query/in-memory-linq/)) | memory. You already have the elements. |

The spelling is identical on purpose — you do not learn two query languages. But an entity query is a *question to the
database*, and a list query is a *loop over what you are holding*. No security rule applies to values you constructed
yourself, and a list of ten million elements is ten million elements in memory.

### 2. What order do the verbs go in?   {#shape}
A query is built the way you would build it in C#: narrow, then order, then page, then finish.

```osy syntax
<Entity>
  .Where(o => <predicate>)          // ONE predicate — combine conditions with && inside it
  .OrderBy(o => k).ThenBy(o => k2)  // any number of keys
  .Skip(n).Take(m)                  // a page (the counts may be runtime values)
  .Include(o => o.Lines)            // pre-load related rows
  .ToList();                        // materialise
```

**A chain may carry more than one `Where`, and they compose** — `xs.Where(a).Where(b)` is `xs.Where(a && b)`,
exactly as in LINQ, and it is still one statement. The two lambdas need not name their parameter the same.

**And the chain need not all be in one place.** Bind it to a `var` and the clauses you add later still join the
SAME query — a chain is deferred until something asks for the answer, exactly as in C#. `.ToList()` is how you say
"read it here". See [Query<T>](https://osysharp.com/reference/query/deferred/), which is also the type you write when a query crosses a function boundary.

The chain ends in a **terminal**, and the terminal is what decides the shape of the answer:

| You want | Terminal | Page |
|---|---|---|
| the rows | `.ToList()` | [ToList](https://osysharp.com/reference/query/tolist/) |
| one row | `.First()` · `.Single()` · `.Last()` · `.FirstOrDefault()` … | [First / Single / Last / ElementAt](https://osysharp.com/reference/query/single-row/) |
| a number | `.Count()` · `.Sum(…)` · `.Average(…)` · `.Min/Max(…)` | [Sum / Average / Min / Max / Count](https://osysharp.com/reference/query/aggregates/) |
| a yes/no | `.Any(…)` | [Where / Single / Count](https://osysharp.com/reference/query/where/) |
| a reshaped row | `.Select(o => new T { … })` | [Select (projections)](https://osysharp.com/reference/query/select/) |
| a row per group | `.GroupBy(…).Select(g => …)` | [GroupBy (and HAVING)](https://osysharp.com/reference/query/group-by/) |

### 3. What is NOT there, and why   {#refusals}
The refusals are deliberate, and each is the same principle: **a verb either becomes SQL or it is refused.** The
alternative — quietly fetching the table and finishing the job in memory — is how an app works fine on your laptop and
falls over on real data.

- **`TakeWhile` / `SkipWhile`** are recognised and refused: they cannot be expressed in SQL. Use `Where` + `OrderBy` +
  `Skip`/`Take`. (C# on a database refuses them too.)
- **`All`, `Contains`, `Aggregate`** as query verbs do not exist. `All(p)` is `!Any(!p)`; for `Contains`, see
  [Dynamic IN (list.Contains in a query)](https://osysharp.com/reference/query/dynamic-in/).
- **Full-text and vector search** are not chain verbs — they are predicates *inside* a `Where`, and they need a
  `[Searchable]` property. See [[Searchable]](https://osysharp.com/reference/memory/searchable/).

### 4. A query sees what you have already written   {#read-your-writes}
A server function commits when it **returns** ([function](https://osysharp.com/reference/function/declaration/)) — so the rows you create partway through it
are not in the database yet. A query in the same function sees them anyway:

```osy title="create, then read back — no commit, no ceremony" test app=query-index
List<Order> BusiestFirst() {
  var a = new Order { Code = "A", Total = 20m };
  var b = new Order { Code = "B", Total = 90m };
  var c = new Order { Code = "C", Total = 40m };

  return Order.Where(o => o.Total > 10m)      // …matches the three above AND anything already stored
              .OrderByDescending(o => o.Total)
              .ToList();                       // …and they are ordered together: 90, 40, 20
}
```

Your pending rows are matched by the `Where`, **sorted into** the order you asked for, paged by `Skip`/`Take`, and
counted by `Count()`. You do not have to commit first, and you should not: committing early would give up the
all-or-nothing guarantee that a fault discards everything the function wrote.

```osy title="proof: the three uncommitted rows come back in order" run app=query-index
[Test]
void A_query_orders_the_rows_this_function_has_not_committed_yet() {
  var busiest = BusiestFirst();

  Assert.Equal(3, busiest.Count);
  Assert.Equal("B", busiest[0].Code);   // 90 — sorted WITH the pending rows, not appended after them
  Assert.Equal("C", busiest[1].Code);   // 40
  Assert.Equal("A", busiest[2].Code);   // 20
}
```

**A pending *edit* counts too**, not just a pending create. Change a field and the very next `Where` decides on the
value you just wrote: a row your edit now matches is returned, and one it no longer matches is not — so *"mark these
delivered, then ask which are still outstanding"* answers the question you actually asked.

```osy title="the filter decides on the value you just wrote" run app=query-index
[TestFixture]
void Stored() {
  var o = new Order { Code = "A", Total = 20m };
  UnitOfWork.Commit();                                     // A is now a stored row, Total = 20
}

[Test(Stored)]
void A_pending_edit_decides_the_filter() {
  var order = Order.Single(o => o.Code == "A");
  order.Total = 500m;                           // a pending edit to a STORED row — no UnitOfWork.Commit()

  Assert.Equal(1, Order.Where(o => o.Total > 100m).ToList().Count);   // it joined the set…
  Assert.Empty(Order.Where(o => o.Total < 100m).ToList());            // …and left the one it was in
  Assert.Equal(500m, order.Total);
}
```

`Count()` and `Any()` answer from the same reconciled set, so `Where(p).Count()` and `Where(p).ToList().Count` cannot
disagree.

**A filter on a *reference* works the same way**, on rows and on links that are both still pending. This is the shape
worth seeing, because a parent and its children are usually created together — neither exists in the database yet, and
the question is still answered from what this function has written.

```osy title="a reference filter over rows that are not committed yet" run app=query-index
[Test]
void A_pending_row_is_found_by_the_reference_you_just_set() {
  var mine   = new Customer { Name = "Mine" };
  var theirs = new Customer { Name = "Theirs" };

  var a = new Order { Code = "A", Total = 10m, Customer = mine };
  var b = new Order { Code = "B", Total = 20m, Customer = theirs };

  Assert.Equal(1, Order.Where(o => o.Customer == mine).ToList().Count);    // …and not B

  a.Customer = theirs;                                                     // re-point it, still no UnitOfWork.Commit()
  Assert.Empty(Order.Where(o => o.Customer == mine).ToList());             // it left the set it was in…
  Assert.Equal(2, Order.Where(o => o.Customer == theirs).ToList().Count);  // …and joined the other
}
```

**One honest limit: `Sum` / `Average` / `Min` / `Max` see only committed rows.** A value aggregate does not include
rows you created and have not committed — so a total is quietly *short* by exactly the rows you just added. `Count()`,
`Any()` and ordinary queries all include them; only the value aggregates do not. If you need one over rows you have
just created or changed, `UnitOfWork.Commit()` first.

### 5. Where the divergences from C# are   {#divergences}
Faithful C# is the goal, so the handful of places the language deliberately differs are worth knowing, because each
one is a bug waiting to happen if you assume otherwise:

- **`Sum` / `Average` / `Min` / `Max` return null on an empty set**, not zero — because SQL does. See
  [Sum / Average / Min / Max / Count](https://osysharp.com/reference/query/aggregates/), which is the page that will save you the most debugging.
- **`Average` is always `decimal`**, whatever the selector's type.
- **`ThenBy` is a synonym for a chained `OrderBy`.** The keys accumulate into one composite sort — a second `OrderBy`
  does *not* re-sort as it would in C#.
- **`Last` requires an `OrderBy`.** "The last row" is meaningless without an order, and the database will not guess.

## See also       {#see-also}
- [Where / Single / Count](https://osysharp.com/reference/query/where/) — the predicate, and `Single` vs `FirstOrDefault` vs `Count`
- [OrderBy / ThenBy](https://osysharp.com/reference/query/ordering/) — `OrderBy` / `ThenBy`, and where they may appear
- [Sum / Average / Min / Max / Count](https://osysharp.com/reference/query/aggregates/) — `Sum` / `Average` / `Min` / `Max` / `Count`, and null-on-empty
- [GroupBy (and HAVING)](https://osysharp.com/reference/query/group-by/) — `GroupBy`, per-group aggregates, and HAVING
- [Select (projections)](https://osysharp.com/reference/query/select/) — projections, and what may precede and follow one
- [Include (pre-loading relations)](https://osysharp.com/reference/query/include/) — pre-loading related rows
- [Child collections (navigating a relation)](https://osysharp.com/reference/query/collections/) — a parent's children, and the FK query you should not write
- [First / Single / Last / ElementAt](https://osysharp.com/reference/query/single-row/) — `First` / `Single` / `Last` / `ElementAt`, and what each does when there is no row
- [Join / LeftJoin / SelectMany](https://osysharp.com/reference/query/joins/) — `Join` / `LeftJoin` / `SelectMany`
- [Traverse (walking a graph)](https://osysharp.com/reference/query/traverse/) — walking a graph to any depth
- [Skip / Take (paging)](https://osysharp.com/reference/query/paging/) — `Skip` / `Take`
- [LINQ over a local list](https://osysharp.com/reference/query/in-memory-linq/) — the same verbs over a `List<T>` you built yourself
- [The security model](https://osysharp.com/reference/security/index/) — why a grant is part of the query rather than a check you remember