Inner, Left, Right, Cross, and Correlated Joins with Cardinality-Aware LINQ

Learning outcomes

A ServiceHub reporting endpoint must list every work order with technician information when present, include technicians who currently have no work, and produce one row per intended relationship—not a hidden cartesian multiplication. The difficulty is not memorizing five LINQ operators; it is preserving cardinality while EF translates expression shape into relational joins. This lesson makes inner, outer, cross, and correlated joins observable in SQL.

1. Start from the stable ServiceHub relationships

// Existing course model — Chapter 09 extends queries, not persistence identity.public sealed partial class WorkOrder{    public int Id { get; private set; }    public string WorkOrderNumber { get; private set; } = string.Empty;    public string CustomerName { get; private set; } = string.Empty;    public WorkOrderPriority Priority { get; private set; }    public int? AssignedTechnicianId { get; private set; }    public Technician? AssignedTechnician { get; private set; }    public ICollection<WorkOrderTag> WorkOrderTags { get; } = new List<WorkOrderTag>();    public Guid Revision { get; private set; }}public sealed class Technician{    public int Id { get; private set; }    public string EmployeeCode { get; private set; } = string.Empty;    public string DisplayName { get; private set; } = string.Empty;    public IReadOnlyCollection<WorkOrder> WorkOrders => _workOrders;    private readonly List<WorkOrder> _workOrders = new();}public sealed class WorkOrderTag{    public int WorkOrderId { get; private set; }    public WorkOrder WorkOrder { get; private set; } = null!;    public int TagId { get; private set; }    public Tag Tag { get; private set; } = null!;    public DateTime AppliedUtc { get; private set; }    public string AppliedBy { get; private set; } = string.Empty;    public int DisplayOrder { get; private set; }}

WorkOrder.AssignedTechnicianId is optional. That one nullable foreign key is enough to demonstrate the semantic difference between “only matched pairs,” “keep every work order,” and “keep every technician.” The database does not infer which side you want preserved; the LINQ shape does.

2. Inner join: keep only matched pairs

var assigned = db.WorkOrders    .Join(        db.Technicians,        w => w.AssignedTechnicianId,        t => (int?)t.Id,        (w, t) => new        {            w.WorkOrderNumber,            Technician = t.DisplayName        });Console.WriteLine(assigned.ToQueryString());

SELECT w.work_order_number, t.display_name AS TechnicianFROM work_orders AS wINNER JOIN technicians AS t    ON w.assigned_technician_id = t.technician_id;

Unassigned work orders disappear because no right-side row satisfies the predicate. A navigation spelling such as Where(w => w.AssignedTechnician != null) may translate to equivalent relational logic; verify the generated SQL rather than assuming navigation syntax changes where work executes.

3. Left join: preserve work orders

Before .NET 10, LINQ expressed a left join through a recognizable GroupJoin → DefaultIfEmpty flattening pattern. EF Core still recognizes that pattern, but .NET 10 adds first-class LeftJoin and EF Core 10 translates it directly.

var classic =    from w in db.WorkOrders    join t in db.Technicians        on w.AssignedTechnicianId equals (int?)t.Id into technicianGroup    from t in technicianGroup.DefaultIfEmpty()    select new    {        w.WorkOrderNumber,        Technician = t == null ? "[unassigned]" : t.DisplayName    };

var modern = db.WorkOrders.LeftJoin(    db.Technicians,    w => w.AssignedTechnicianId,    t => (int?)t.Id,    (w, t) => new    {        w.WorkOrderNumber,        Technician = t == null ? "[unassigned]" : t.DisplayName    });

Keep the old pattern in your mental toolbox because existing codebases use it, but prefer the direct operator when your target is .NET 10/EF Core 10 and team compatibility is clear.

4. Right join: preserve technicians

var technicianCoverage = db.WorkOrders.RightJoin(    db.Technicians,    w => w.AssignedTechnicianId,    t => (int?)t.Id,    (w, t) => new    {        t.EmployeeCode,        t.DisplayName,        WorkOrder = w == null ? null : w.WorkOrderNumber    });Console.WriteLine(technicianCoverage.ToQueryString());

Right join retains every second-source row, so a technician with no assignment still appears. EF Core 10 supports the .NET 10 RightJoin operator. Provider SQL dialect/support still matters; if a provider cannot translate an equivalent form, reversing the inputs and using a left join is a semantics-preserving alternative for this two-source case.

5. Cross join: every pair is a real operation

var combinations =    from w in db.WorkOrders    from t in db.Technicians    select new { w.Id, TechnicianId = t.Id };Console.WriteLine(combinations.ToQueryString());

SELECT w.work_order_id, t.technician_idFROM work_orders AS wCROSS JOIN technicians AS t;

If the lab has 1,000 work orders and 30 technicians, this shape has 30,000 rows before any later filter. A cross join is not inherently wrong—matrix/scenario generation can need one—but it should be deliberate and cardinality-bounded.

6. Correlated SelectMany can translate very differently

var query =    from w in db.WorkOrders    from t in db.Technicians.Where(t => t.Id == w.AssignedTechnicianId)    select new { w.WorkOrderNumber, t.DisplayName };

When the correlation appears in a predicate, relational providers commonly translate it as a join. If the inner selector references the outer row in a non-predicate projection, EF may need CROSS APPLY/OUTER APPLY. Microsoft documents that SQLite does not support APPLY operators, so such shapes can fail translation even when SQL Server can run them.

var providerSensitive =    from w in db.WorkOrders    from label in db.Technicians.Select(        t => w.WorkOrderNumber + ":" + t.EmployeeCode)    select label;// Do not assume this translates on SQLite; inspect/execute against your provider.

7. Deliberately wrong: multiplicative join hidden inside a report

var rows = await (    from w in db.WorkOrders    from tag in db.Tags                 // no relationship predicate    where w.Priority == WorkOrderPriority.High    select new { w.WorkOrderNumber, tag.Name })    .ToListAsync(ct);

The result is “correct” according to the expression: each high-priority work order is paired with every tag. The bug is the query definition, not EF. Repair it by traversing the actual join entity/navigation or adding an explicit join predicate.

var rows = await db.Set<WorkOrderTag>()    .Where(x => x.WorkOrder.Priority == WorkOrderPriority.High)    .OrderBy(x => x.WorkOrderId)    .ThenBy(x => x.DisplayOrder)    .Select(x => new    {        x.WorkOrder.WorkOrderNumber,        Tag = x.Tag.Name,        x.DisplayOrder    })    .ToListAsync(ct);

8. Evidence: count before you blame materialization

For every join-heavy query, record source row counts and expected upper/lower bounds. Compare CountAsync(), generated SQL, and a small sample before returning a large projection. Include is a loading tool, not a replacement for reasoning about relational multiplication; Chapter 10 will examine single-vs-split loading in depth.

9. Hands-on lab: prove cardinality

1. Use the disposable servicehub-lab.db; seed at least one unassigned work order and one technician with no work orders.
2. Run inner, left, and right-join queries and record returned row counts plus ToQueryString().
3. Run the explicit cross join only after calculating the expected product; cap the final materialization with Take.
4. Run the correlated predicate form and inspect whether SQLite emits a normal join.
5. Try the APPLY-like shape and capture the translation outcome rather than assuming success.
6. Introduce the deliberate unrelated-tag cross join, then repair it through WorkOrderTag.

10. Production judgment and bridge

Choose join shape from business row-preservation semantics first, then inspect provider SQL and cardinality. Prefer direct .NET 10 LeftJoin/RightJoin where the stable baseline permits them, but retain understanding of classic patterns for older code. Lesson 2 now compresses rows with GroupBy and aggregates, where the boundary between SQL grouping and client-created IGrouping objects becomes the main translation constraint.

Authoritative references

• What's New in EF Core 10 — .NET 10 LeftJoin/RightJoin support
• Complex Query Operators - EF Core — Join, GroupJoin, SelectMany, GroupBy, LEFT JOIN and APPLY translation
• Relationships - EF Core — foreign-key/navigation semantics
• How Queries Work - EF Core — translation/execution pipeline
• SQLite SELECT documentation — SQLite join syntax and semantics