# Child collections (navigating a relation)

> A parent's child collection — `order.Lines` — is not a loaded array. It is a QUERY, correlated to that parent, and every LINQ verb works on it: filter it, sum it, ask if any child matches. Which is why you navigate to children through the parent rather than querying the child table with a foreign-key filter: the collection already knows which parent it belongs to, and the compiler writes that condition for you.

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

## Summary        {#summary}
A child collection is a **query**, not a loaded list:

```osy title="a collection is a query, correlated to its parent" test app=query-collections
entity Order {
  [Required, MaxLength(40)] string Code;
  [ForeignKey(Order)] Line[] Lines;          // the collection
}

entity Line {
  [Required] Order Order;                     // the back-reference that defines it
  [MaxLength(60)] string Sku;
  int Qty;
  decimal Amount;
}

decimal BigLinesTotal(Order order) {
  return order.Lines
              .Where(l => l.Qty > 1)
              .Sum(l => l.Amount);            // ONE SQL statement, correlated to THIS order
}
```

`order.Lines` means *"the lines whose `Order` is this order"* — and because that condition is implicit, everything you
chain onto it is added to it. The `Where` above narrows a set the database has not yet built.

## Signature      {#signature}
```osy syntax
parent.Children                              // a query: the children of THIS parent
parent.Children.Where(c => p)                // …narrowed
parent.Children.Count()  /  .Any(c => p)     // …counted / tested
parent.Children.Sum(c => v)                  // …aggregated (0 over no children — see query-aggregates)
parent.Children.OrderBy(c => k).ToList()     // …ordered and materialised
foreach (var c in parent.Children) { … }     // …iterated
```

## Description    {#description}

### Is `order.Lines` a fetched array, or a query?   {#a-query}
Reading `order.Lines` does not hand you an array that was fetched earlier. It hands you a **question**, which is
answered when you ask it. Three things follow:

- **Everything you chain is pushed into the database.** `order.Lines.Where(l => l.Qty > 1).Sum(l => l.Amount)` is one
  statement with a `WHERE` and a `SUM` — not "fetch all the lines, then filter and add them up in memory".
- **`.Count()` does not fetch the children.** It counts them. A parent with 10,000 lines costs the same to count as
  one with three.
- **Touching it in a loop over parents is N+1.** That is exactly what [`Include`](https://osysharp.com/reference/query/include/) is for — pre-load
  the children with the parents and the navigation becomes free.

### Go through the parent, not around it   {#through-the-parent}
You can always ask the child table directly, and sometimes it is genuinely what you want:

```osy syntax
order.Lines.Where(l => l.Qty > 1)            // ✅ navigate — the parent condition is implicit
Line.Where(l => l.Order == order && l.Qty > 1)   // ⚠ the same rows, spelled the long way
```

Both are legal — the compiler does not stop you, and there is no correctness difference. But **prefer the
collection**, and the reason is one you will feel later rather than now:

- **You cannot get the join condition wrong** if you never write it. The `l.Order == order` in the second form is a
  condition you have to remember, on every query, forever; the first form has it built in.
- **It reads as what it is.** `order.Lines` is "this order's lines". The FK filter is a re-derivation of a fact the
  model already knows.
- **It is the shape the client's data layer understands.** A collection navigated from a parent stays coherent with
  the parent when it changes; a hand-rolled FK query is a detached result that does not.

Reach for the root query (`Line.Where(…)`) when you are genuinely asking a question **about all the children** —
"every backordered line across every order" — rather than about one parent's. That is a different question, and the
root query is the honest way to write it.

### How do I filter parents by a fact about their children?   {#in-a-predicate}
A collection used inside a `Where` on the *parent* lowers to a correlated subquery — which is how you filter parents
by a fact about their children:

```osy title="orders that contain a backordered line" test app=query-collections
List<Order> WithBackorder() {
  return Order.Where(o => o.Lines.Any(l => l.Qty == 0)).ToList();   // → WHERE EXISTS (…)
}

List<Order> Large() {
  return Order.Where(o => o.Lines.Count() > 10).ToList();           // → WHERE (SELECT COUNT(*) …) > 10
}
```

No lines are fetched by either. The database answers the question about the children while it is deciding which
parents to return.

## Examples       {#examples}
Iterating a parent's children, and the `Include` that makes doing it over many parents affordable:

```osy title="the loop, and the one word that makes it cheap" test app=query-collections
decimal InvoiceRun() {
  var orders = Order.Include(o => o.Lines).ToList();   // ← without this, one query PER order below

  decimal total = 0m;
  foreach (var o in orders) {
    foreach (var l in o.Lines) {
      total = total + l.Amount;
    }
  }
  return total;
}
```

## See also       {#see-also}
- [Include (pre-loading relations)](https://osysharp.com/reference/query/include/) — pre-loading children so a loop over parents is one query, not N+1
- [relations](https://osysharp.com/reference/entity/relations/) — declaring the relation (`[ForeignKey(...)]` and the collection it defines)
- [Sum / Average / Min / Max / Count](https://osysharp.com/reference/query/aggregates/) — `Sum`/`Count` over a collection, and what each answers over no rows
- [Querying data](https://osysharp.com/reference/query/index/) — the three things you can query, and what each costs