Join / LeftJoin / SelectMany

Summary#

Combine two entities into one row shape:

class Row {
  public string OrderCode;
  public string CustomerName;
}

entity Customer {
  [Required, MaxLength(60)] string Ref;
  [MaxLength(120)] string Name;
}

entity Order {
  [Required, MaxLength(40)] string Code;
  [MaxLength(60)] string CustomerRef;      // related by a VALUE, not by a reference
  [ForeignKey(Order)] Line[] Lines;
}

List<Row> Rows() {
  return Order.Join(Customer,
                    o => o.CustomerRef,     // the key on the left
                    c => c.Ref,             // the key on the right
                    (o, c) => new Row { OrderCode = o.Code, CustomerName = c.Name })
            .ToList();
}

Signature#

<Entity>.Join(<Other>, a => aKey, b => bKey, (a, b) => projection)       // INNER JOIN — matches on both sides
<Entity>.LeftJoin(<Other>, a => aKey, b => bKey, (a, b) => projection)   // LEFT JOIN — every left row; null on the right
<Entity>.SelectMany(a => a.Children, (a, c) => projection)               // flatten: one row per child
<Entity>.SelectMany(a => <Other>, (a, b) => projection)                  // every pairing (a cross join)

The range-variable names must be the same in the key selectors and the result selector — o and c above. That is not a style rule; the compiler binds them by name.

Description#

First: you usually do not need a join#

This is the most useful thing on the page. If the two entities are related by a declared relation, you do not join them — you navigate:

order.Customer.Name                          // a reference: just read it
order.Lines.Sum(l => l.Amount)               // a collection: query it ([Child collections (navigating a relation)](/reference/query/collections/))
Order.Include(o => o.Lines.Product).ToList() // pre-load a whole graph ([Include (pre-loading relations)](/reference/query/include/))

The relation already knows how the rows connect. Writing the join condition again by hand is a re-derivation of a fact the model holds — and a chance to get it wrong.

Reach for a join when there is no relation to navigate: two entities related by a shared value (a code, a reference, a slug) rather than by a foreign key. That is what the example above is — CustomerRef is a string that happens to match Customer.Ref, and no relation ties them.

Join vs LeftJoin#

• Join keeps only the rows that match on both sides. An order whose CustomerRef matches no customer simply is not in the result — which is sometimes exactly right, and sometimes how a row silently disappears from a report.
• LeftJoin keeps every row on the left, and hands you null on the right where nothing matched. Reach for it when the left side is the thing you are reporting on and the right side is extra detail.

class Report {
  public string OrderCode;
  public string? CustomerName;   // null when nothing matched — that is the point
}

List<Report> AllOrders() {
  return Order.LeftJoin(Customer,
                        o => o.CustomerRef,
                        c => c.Ref,
                        (o, c) => new Report { OrderCode = o.Code, CustomerName = c.Name })
              .ToList();
}

If a report is missing rows you know exist, an inner join is the first thing to suspect.

How do I get one row per child? — SelectMany#

SelectMany turns a parent and its children into one row per child, which is the shape a flat export or a line -level report wants:

class LineRow {
  public string OrderCode;
  public string Sku;
  public decimal Amount;
}

entity Line {
  [Required] Order Order;
  [MaxLength(60)] string Sku;
  decimal Amount;
}

List<LineRow> Flat() {
  return Order.SelectMany(o => o.Lines,
                          (o, l) => new LineRow { OrderCode = o.Code, Sku = l.Sku, Amount = l.Amount })
               .ToList();
}

Note what it is not: this does not fetch orders and then their lines. It is one statement — a join on the child's foreign key — returning line-shaped rows.

Given an unrelated entity instead of a collection, SelectMany produces every pairing of the two (a cross join). That is occasionally what you want and much more often a mistake; be sure.

What does a join hand back?#

Each of these takes a result selector, so a join always ends in a shape you named — usually a class. There is no "joined entity" type to hand back: you say what the combined row looks like, and that is what you get. A trailing Where / OrderBy / Take / Skip may follow, and the chain must end in that projection.

These are entity-only. There is no join over a local list yet.

A join is one statement over stored rows, and each side reads only the rows its own security { } lets the caller read — a row they may not read is not there to join to, so Join drops it and LeftJoin answers null for it. While the unit of work holds an uncommitted create, edit or delete of any entity the join reads, the join is read in memory instead, over the rows this unit of work actually has: each side read under its own security { } exactly as above, then paired, filtered, ordered and projected here. So a row you created a moment ago is joined like any other, and you do not have to commit to read it back. On is matched with C#'s equality, null included.

Over entities in a data source#

A join between two entities of the same data source is one statement the source runs, when its provider declares ValueJoins. The joined entity is read under its own security block first, so a row the reader may not see is not there to match: Join drops the outer row, LeftJoin answers null for it. Joining one of the app's own entities to an entity in a data source is a compile error — no store holds both; read one side first with its own query.

Examples#

See the fences above — an inner join on a value, the left join that keeps the unmatched rows, and SelectMany for a line-level flatten.

See also#

• Child collections (navigating a relation) — navigating a declared relation, which is what you want most of the time
• Include (pre-loading relations) — pre-loading a graph instead of flattening it
• Select (projections) — the projection a join ends in
• relations — declaring the relation that removes the need for a join