Include (pre-loading relations)

Summary#

Include pre-loads the relations your results are about to walk:

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#

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

It changes performance, not results#

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#

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.

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

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

A dotted path in one lambda walks further down, loading every step:

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#

Include composes with Where, OrderBy, Skip/Take, ToList() and the single-row terminals (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 projection — a projection does not return entity rows. Select what you need.
• before a GroupBy — a group is not a row.
• with the set operators — apply it after materialising.

On a list you already hold#

A local list 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.

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 runs — a state's enter, 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:

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#

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:

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.

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#

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.

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 returns, 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:

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

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:

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:

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#

Include on a single-row terminal — the shape a detail page uses:

Order? Detail(string code) {
  return Order.Where(o => o.Code == code)
              .Include(o => o.Lines.Product)
              .FirstOrDefault();
}

See also#

• Child collections (navigating a relation) — the navigation Include makes free (and what a child collection actually is)
• relations — declaring the relation in the first place
• Select (projections) — the other answer to "I only need part of this": ask for fewer columns
• Querying data — the shape of a query chain