# Include (pre-loading relations)

> Pre-load the related rows a query's results are about to navigate to. `Include(o => o.Lines)` does not change what comes back — the same rows, the same type — it just means the children are already in hand, so the loop that walks them costs nothing instead of firing one query per parent. It is the fix for the N+1 problem, and the only reason you ever need to think about it.

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

## Summary        {#summary}
`Include` pre-loads the relations your results are about to walk:

```osy title="one query for the orders, one for all their lines" test app=query-include
entity Order {
  [Required, MaxLength(40)] string Code;
  [ForeignKey(Order)] Line[] Lines;
}

entity Line {
  [Required] Order Order;
  Product Product;                 // the reference the nested Include walks to
  [MaxLength(60)] string Sku;
  decimal Amount;
}

decimal TotalOfRecentOrders() {
  var orders = Order.OrderByDescending(o => o.Code)
                    .Take(50)
                    .Include(o => o.Lines)      // ← the lines come back with the orders
                    .ToList();

  decimal total = 0m;
  foreach (var o in orders) {
    foreach (var l in o.Lines) { total = total + l.Amount; }   // already in memory — no query in this loop
  }
  return total;
}
```

Delete the `Include` and that code still works, and it costs one more read: the first order's lines are read on their
own, and from the second order on the platform loads every remaining order's lines in one statement (see
[[query-include#n-plus-one]]). So **in a function body it is a *performance* declaration, not a semantic one** — it
says up front what the code needs, which makes it one read instead of two.

⚠ **On a page it is REQUIRED, not an optimisation.** The sentence above is true of code that runs on the server,
where a relation you did not pre-load is fetched on demand. A page renders on the client, which has no such
fallback: it can only walk what the query actually brought back. See [[query-include#on-a-page]] before you leave
one out.

## Signature      {#signature}
```osy syntax
<Query>.Include(o => o.Collection)          // a child collection
<Query>.Include(o => o.Reference)           // an entity reference
<Query>.Include(o => o.Lines.Product)       // NESTED — a dotted path in ONE lambda
<Query>.Include(a).Include(b)               // repeatable — they accumulate
```

A **member-selector lambda**, not a string. There is no `ThenInclude`: nesting is a dotted path inside the one lambda,
and every step of the path is loaded, not just the leaf.

## Description    {#description}

### It changes performance, not results   {#not-semantics}
An `Include` returns **exactly the same rows, of exactly the same type**, as the query without it. It adds nothing to
the `SELECT`, filters nothing, and never appears in the row shape. All it does is load the related rows into memory
*with* the parents, so the navigation you were going to do anyway is already paid for.

This is worth internalising, because it explains why you can add or remove one freely **in a function body**: an
`Include` there cannot change what your code computes — only how many round trips it takes to compute it. If adding
one changes an answer, the answer was wrong before.

**That freedom is the server's, and only the server's.** The next section is the other half.

### On a page, it IS semantic — and leaving it out is an error   {#on-a-page}

A page's render runs on the client against the rows the query returned. There is no lazy load out there: a relation
that was not included did not travel, so the reference holds its raw key rather than the row it names. Walking it
does not fetch anything and does not return empty — **it fails**, because you asked a key for a property only a row
has.

```osy syntax
// the query behind the page
var item = Item.Where(i => i.Code == code)
               .Include(i => i.Owner)              // ← REQUIRED: the render reads Owner.Name
               .Include(i => i.Links.Target)       // ← REQUIRED: and it walks THROUGH Links to each Target
               .FirstOrDefault();

render {
  Text(item.Owner.Name);                            // without the first Include: no row, no Name
  foreach (var l in item.Links) { Text(l.Target.Code); }   // without the SECOND: the links came, their targets did not
}
```

Two things follow, and both cost people time:

**A nested walk needs the nested path.** Including the collection is not enough — `Include(i => i.Links)` brings the
links and stops there, so `l.Target.Code` still has nothing to read. The dotted form loads every step.

**Do not conclude anything from a page that works without one.** Whether an un-included reference resolves depends
on whether *some other query on the same page* already loaded that row, because they share one store. So the same
render can be correct on a page that happens to list Owners elsewhere and fail on a page that does not — identical
code, different neighbours. Include what you walk, and the question never arises.

### The N+1 problem, which is the reason it exists   {#n-plus-one}
Fetch 50 orders, then loop over each one's lines. Done naively, each `o.Lines` is a *fresh query* — 1 query for the
orders and 50 for the lines: fast on your machine with 3 orders and an outage on a real database with 5,000.

The platform does not do it naively. Rows one query answered are remembered as read together, so when a SECOND of them
asks for its `Lines`, the lines of all the rest (up to 500 at a time) are loaded in one statement. Without an `Include`
the loop is therefore 3 reads (the orders, the first order's lines, everyone else's), however many orders there are.
The same holds on a page whose cards each ask about their row's children while the server renders it.

**One case still costs a read per row: a loop that creates children of that same type as it goes.** While the unit of
work holds an unsaved `Line`, a batch read from the database would not show it, so each `o.Lines` is read on its own
(and correctly includes the new line). The `cost-n-plus-one` lint names that loop.

With the `Include`, the children arrive with the parents in the first statement, the loop touches memory in every
case, and the code states what it needs. A function you call with `o.Lines` reads the same included rows — they are
loaded once for the whole request, not once per call.

### How do I load two levels down?   {#nesting}
A dotted path in one lambda walks further down, loading every step:

```osy title="orders → their lines → each line's product" test app=query-include
entity Product {
  [Required, MaxLength(60)] string Name;
  decimal Price;
}

List<Order> WithEverything() {
  return Order.Include(o => o.Lines.Product)     // loads Lines AND each Line's Product
              .ToList();
}
```

There is no `ThenInclude` to chain — the path is the nesting. To load two *different* branches, call `Include` twice.

### Where it may appear   {#position}
`Include` composes with `Where`, `OrderBy`, `Skip`/`Take`, `ToList()` and the [single-row
terminals](https://osysharp.com/reference/query/single-row/) (an `Include` on a `First()` is perfectly sensible — one parent, its children in hand).

It is **refused** in three places, each for the same reason — there would be no entity rows for it to attach the
relations to:

- before a [`Select`](https://osysharp.com/reference/query/select/) projection — a projection does not return entity rows. Select what you need.
- before a [`GroupBy`](https://osysharp.com/reference/query/group-by/) — a group is not a row.
- with the [set operators](https://osysharp.com/reference/query/set-operators/) — apply it after materialising.

### On a list you already hold   {#on-a-list}
A local [list](https://osysharp.com/reference/query/in-memory-linq/) takes `Include` too, and for exactly the same reason a server query does:
holding the ROWS is not the same as holding what they REFER to. A row carries a reference as a value, so reading
through it in a filter has to fetch the row it names — once per row, unless it was included first.

```osy title="include a reference before filtering on it in memory" test app=query-include-in-memory
entity Supplier { [Required] [MaxLength(80)] string Name; security { allow read, create when IsAuthenticated || IsAnonymous; } }
entity Part {
  [Required] [MaxLength(80)] string Code;
  [Required] Supplier Supplier;
  security { allow read, create when IsAuthenticated || IsAnonymous; }
}

int AcmeParts() {
  var parts = Part.ToList();
  return parts.Include(p => p.Supplier).Where(p => p.Supplier.Name == "Acme").Count();
}
```

The load is **batched**: one pass per hop, over the distinct references in the whole list — not one read per row. A
deeper path (`p => p.Supplier.Region`) loads the second hop across everything the first hop returned, so the cost
does not grow with the number of rows.

In a function like this one the filter also works **without** the `Include`: its rows are tracked, so each
`p.Supplier` is loaded when it is read — exactly as a `foreach` over the same list would load it. That is the
[[#n-plus-one|N+1]] shape, so the `Include` is still the one to write; it changes what the filter costs, never what it
answers ([[#not-semantics]]).

The same holds in every **server** body, not only a function: a class method, and everything a
[workflow](https://osysharp.com/reference/workflow/index/) runs — a state's [`enter`](https://osysharp.com/reference/workflow/enter-exit/), an event handler, a route arm. A workflow
body runs on the server over tracked rows, in the same interpreter as a function, so a read through a reference inside
its `Where` resolves exactly as `p.Supplier.Name` written on a line of its own does:

```osy title="a workflow body filters a list through a reference" test app=query-include-in-workflow
using Osysharp.Workflow;

entity Supplier { [Required] [MaxLength(80)] string Name; security { allow read, create when IsAuthenticated || IsAnonymous; } }
entity Part {
  [Required] [MaxLength(80)] string Code;
  [Required] Supplier Supplier;
  security { allow read, create when IsAuthenticated || IsAnonymous; }
}

enum AuditStatus { Counting, Done }
entity Audit {
  [MaxLength(80)] string SupplierName;
  AuditStatus Status;
  int Parts;
  security { allow read, create, update when IsAuthenticated || IsAnonymous; }
}

workflow AuditFlow {
  Tracks    = Audit.Status;
  Autostart = true;
  Initial   = Counting;

  state Counting {
    enter {
      var parts = Part.ToList();
      this.Item.Parts = parts.Where(p => p.Supplier.Name == this.Item.SupplierName).Count();
      goto Done;
    }
  }

  terminal success Done { }
}
```

A body that runs again after a crash needs nothing extra for this. A read is not a recorded step: work that did not
commit is simply done again, so the rows and their references are read again from the database, as the `foreach`
spelling of the same filter would read them ([[workflow-automatic-durability#not-steps]]).

Comparing a reference by its **identity** needs nothing loaded anywhere: `p.Supplier.Id == supplierId` reads the key the
row already holds, on the server and on the client alike, so it is never refused and never adds an `Include`.

### In a component, it is added for you   {#auto}
A component's query fetches what its `render` reads. If the render navigates a reference the query did not include,
the compiler adds the `Include` rather than refusing the code:

```osy title="the render reads a reference the query never included — the Include is added" syntax
live var reports = Report.ToList();      // + .Include(Owner) — added, because the render below reads it

render {
  foreach (var r in reports) { Text(r.Owner.Email); }
}
```

Reading `r.Owner.Email` **is** the request for `Owner` — there is no program that wants the read and not the fetch —
so requiring a second statement of the same fact only creates something that can drift out of step with the first.
The two spellings behave identically: a reference navigated inside a client-side `Where` is the same demand as one
read in an element, and both are added. So does the same filter inside one of the component's **methods or actions**
— `int OnPlan(string name) { return subscriptions.Where(s => s.Plan.Name == name).Count(); }` widens `subscriptions`
exactly as the render would.

This is also how you end up on the efficient path without having to know about it. What gets added is an **eager**
load — one query with a join, batched per hop as described above — so the default is the one that avoids
[[#n-plus-one|N+1]], not a fetch per row.

It is not invisible: the editor shows what was added as a hint after the query (`+ .Include(Owner)`), so what the
query costs is still readable at the point you are reading it.

**Through a component's props, too.** Rows handed to a component — one row (`Card(Product product)`) or a list of
them (`Shelf(List<Product> products)`) — arrived from the caller's query, so what the component reads off them is
the caller's to fetch, and it is added to the caller's query exactly as a read in the caller's own render would be.
Every ordinary way of reaching a row of a list prop counts: a `foreach` over it, `products[0]`, `products.First()`,
a render `var` bound to one of those, and the element of a lambda over it.

```osy title="a list prop's rows read their collections — the page's query fetches them" test app=query-include-list-prop
entity Product {
  [Required, MaxLength(60)] string Name;
  [ForeignKey(Product)] Photo[] Photos;
  security { allow read when IsAnonymous || IsAuthenticated; }
}
entity Photo {
  [Required] Product Product;
  [Required, MaxLength(80)] string Alt;
  security { allow read when IsAnonymous || IsAuthenticated; }
}

[Composable]
component Shelf(List<Product> products) {
  render {
    Stack {
      if (products.Count > 0) { Text(products[0].Name + " leads, with " + products[0].Photos.Count() + " photos"); }
      foreach (var p in products) {
        foreach (var photo in p.Photos) { Text(photo.Alt); }
      }
    }
  }
}

[Route("/shelf")]
[AllowAnonymous]
component ShelfPage() {
  live var products = Product.OrderBy(p => p.Name).ToList();   // + .Include(Photos) — added, because Shelf reads it
  render { Shelf(products); }
}
```

**The refusal is still there when the compiler cannot satisfy the demand** — a read through a reference on something
that is not a component query field (a list the component built itself, a derived `live var`) is still a compile error
(a server body — a function, a method, a workflow body — never raises it),
and it names all three ways out: include it on the query that produced the rows, compare by `.Id`, or filter on the
server. Dropping such a read silently is the one
outcome worse than refusing it: the page would render a blank where the value should be.

### Rows a page holds through a function   {#through-a-function}

A page can hold its rows through a server function instead of a query of its own — `live var o = OrderById(id);`.
Nothing on the page can be widened then: the query is inside the function. What the rows carry is what the
function's **answer** carries, and that is decided by what the server sends back with it:

- **every reference of each returned row, one hop** — always, whatever the query included;
- **any deeper reference the function's query included** — `.Include(o => o.Winery.Region)` sends the region too;
- **never a collection.** A row reaches the browser as its own values, and an included collection stays on the
  server.

```osy title="the function includes what the page and its component read" test app=query-include-function-answer
entity Region { [Required, MaxLength(60)] string Name; security { allow read when IsAnonymous || IsAuthenticated; } }
entity Winery {
  [Required, MaxLength(60)] string Name;
  [Required] Region Region;
  security { allow read when IsAnonymous || IsAuthenticated; }
}
entity Shipment {
  [Required, MaxLength(40)] string Code;
  [Required] Winery Winery;
  security { allow read when IsAnonymous || IsAuthenticated; }
}

[AllowAnonymous]
public Shipment? ShipmentByCode(string code) {
  return Shipment.Where(s => s.Code == code).Include(s => s.Winery.Region).FirstOrDefault();
}

[Composable]
component ShipmentOrigin(Shipment s) {
  render { Text(s.Winery.Name + ", " + s.Winery.Region.Name); }
}

[Route("/shipment/{code}")]
[AllowAnonymous]
component ShipmentPage(string code) {
  live var s = ShipmentByCode(code);
  render { if (s != null) { Text(s.Code); ShipmentOrigin(s); } }
}
```

When a function has several `return`s, the page can rely only on what **every** one of them includes. A read the
answer does not carry is refused — at the read, or at the component it is handed to — and the refusal names the
function, because its query is the one to change:

```osy title="what the page is told when the function did not include it" syntax
// ShipmentByCode included only Winery:
//   'ShipmentOrigin' reads 's.Winery.Region', so the row passed here must carry it — but this row is the answer of
//   `ShipmentByCode(…)` … Add it to the query inside `ShipmentByCode` (`.Include(x => x.Winery.Region)`).
```

For a collection no `Include` inside the function can help: read it through a query on the page — make the row itself
a `live var` query, or give the component the rows from one.

### With a projection `Select`, there is nothing left to pre-load   {#with-a-projection}

`Include` and a projection `Select` do not combine, in either order, and the refusal says so. It is not a limitation
of projections — it is that the two ask for the same thing. A projection decides what comes back, so the eager load
has nothing left to pre-load. (C# on a database behaves the same way: an `Include` is ignored once the query ends in
a projection.)

**A projection reaches through a reference on its own**, which is the part worth knowing — it needs no `Include`
anywhere:

```osy title="a column of the related row — no Include" test app=include-projection
entity CostCenter {
  [Required, MaxLength(80)] string Name;
  security { allow read, create when IsAnonymous || IsAuthenticated; }
}

entity ExpenseLine {
  [Required, MaxLength(80)] string Description;
  decimal Amount;
  CostCenter CostCenter;
  security { allow read, create when IsAnonymous || IsAuthenticated; }
}

List<string> CentreNames() {
  return ExpenseLine.Where(l => l.Amount > 0)
                    .Select(l => l.CostCenter.Name)     // reaches through the reference — no Include
                    .ToList();
}
```

So there are two shapes, and the one you want depends on whether you need a **column** or the **row**:

| you want | write |
|---|---|
| a column of the related row | `.Select(l => l.CostCenter.Name)` — no `Include` |
| the related row itself | `.Include(l => l.CostCenter).ToList()` — no `Select` |

Adding `.Include(…)` to the first is refused rather than quietly dropped, deliberately: `Include` is the fix for
N+1 and nothing else, so accepting and ignoring it would tell you that you had solved a cost problem you had not
touched.

## Examples       {#examples}
Include on a single-row terminal — the shape a detail page uses:

```osy title="one order, with its lines already loaded" test app=query-include
Order? Detail(string code) {
  return Order.Where(o => o.Code == code)
              .Include(o => o.Lines.Product)
              .FirstOrDefault();
}
```

## See also       {#see-also}
- [Child collections (navigating a relation)](https://osysharp.com/reference/query/collections/) — the navigation `Include` makes free (and what a child collection actually is)
- [relations](https://osysharp.com/reference/entity/relations/) — declaring the relation in the first place
- [Select (projections)](https://osysharp.com/reference/query/select/) — the other answer to "I only need part of this": ask for fewer columns
- [Querying data](https://osysharp.com/reference/query/index/) — the shape of a query chain