Child collections (navigating a relation)

Summary#

A child collection is a query, not a loaded list:

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#

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#

Is order.Lines a fetched array, or 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 is for — pre-load the children with the parents and the navigation becomes free.

Go through the parent, not around it#

You can always ask the child table directly, and sometimes it is genuinely what you want:

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

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:

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#

Iterating a parent's children, and the Include that makes doing it over many parents affordable:

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#

• Include (pre-loading relations) — pre-loading children so a loop over parents is one query, not N+1
• relations — declaring the relation ([ForeignKey(...)] and the collection it defines)
• Sum / Average / Min / Max / Count — Sum/Count over a collection, and what each answers over no rows
• Querying data — the three things you can query, and what each costs