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.

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