# Join / LeftJoin / SelectMany

> Combine two entities into one result. `Join` keeps the rows that match on both sides; `LeftJoin` keeps every row on the left and gives you null on the right where there is no match; `SelectMany` flattens a parent and its children into one row per child. Most of the time you do NOT need any of them — a relation you declared is navigated, not joined — so reach for these when the two things are related by a VALUE rather than by a reference.

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

## Summary        {#summary}
Combine two entities into one row shape:

```osy title="an inner join on a value" test app=query-joins
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      {#signature}
```osy syntax
<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    {#description}

### First: you usually do not need a join   {#usually-not}
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*:

```osy syntax
order.Customer.Name                          // a reference: just read it
order.Lines.Sum(l => l.Amount)               // a collection: query it ([Child collections (navigating a relation)](https://osysharp.com/reference/query/collections/))
Order.Include(o => o.Lines.Product).ToList() // pre-load a whole graph ([Include (pre-loading relations)](https://osysharp.com/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`   {#inner-vs-left}
- **`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.

```osy title="every order, even the ones with no matching customer" test app=query-joins
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}
`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:

```osy title="one row per line, carrying its order's code" test app=query-joins
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?   {#projection}
Each of these takes a **result selector**, so a join always ends in a shape you named — usually a
[`class`](https://osysharp.com/reference/query/select/). 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](https://osysharp.com/reference/query/in-memory-linq/) yet.

## Examples       {#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       {#see-also}
- [Child collections (navigating a relation)](https://osysharp.com/reference/query/collections/) — navigating a declared relation, which is what you want most of the time
- [Include (pre-loading relations)](https://osysharp.com/reference/query/include/) — pre-loading a graph instead of flattening it
- [Select (projections)](https://osysharp.com/reference/query/select/) — the projection a join ends in
- [relations](https://osysharp.com/reference/entity/relations/) — declaring the relation that removes the need for a join