LINQ Join Types — Inner, Left, Cross, and GroupJoin

Why Explicit Joins vs Navigation Properties

With EF Core, you usually join through navigation properties, not explicit join syntax. But explicit joins are useful when:

The relationship is not modeled in EF Core
You join on non-FK columns
You are working with in-memory collections (no EF Core)
You need a left outer join that would not naturally fall out of navigation includes

Inner Join

Returns only elements that have a match in both sequences.

// Explicit join syntax
var doctorPrescriptions = from rx in db.Prescriptions
                          join doc in db.Users
                            on rx.PrescriberId equals doc.Id
                          where doc.Department == "Cardiology"
                          select new
                          {
                              rx.Id,
                              rx.DrugName,
                              DoctorName = doc.FullName
                          };

// Method syntax
var result = db.Prescriptions
    .Join(
        db.Users,
        rx  => rx.PrescriberId,   // outer key
        doc => doc.Id,             // inner key
        (rx, doc) => new { rx.Id, rx.DrugName, DoctorName = doc.FullName })
    .ToList();

// EF Core navigation property (preferred when relationship is modeled)
var result2 = await db.Prescriptions
    .Include(rx => rx.Prescriber)
    .Where(rx => rx.Prescriber.Department == "Cardiology")
    .Select(rx => new { rx.Id, rx.DrugName, rx.Prescriber.FullName })
    .ToListAsync();

Left Outer Join with DefaultIfEmpty

Returns all elements from the left sequence, and matching elements from the right. If no match, the right side is null.

// All patients, with their most recent prescription (if any)
var patientsWithRx = from p in db.Patients
                     join rx in db.Prescriptions
                       on p.Id equals rx.PatientId into rxGroup
                     from rx in rxGroup.DefaultIfEmpty()  // left outer join
                     select new
                     {
                         p.Id,
                         p.FullName,
                         LastDrug = rx == null ? "None" : rx.DrugName,
                         LastIssued = rx == null ? (DateTime?)null : rx.IssuedAt
                     };

// SQL: SELECT p.Id, p.FullName, rx.DrugName, rx.IssuedAt
//      FROM Patients p
//      LEFT JOIN Prescriptions rx ON p.Id = rx.PatientId

GroupJoin — Hierarchical Results

GroupJoin is like a left outer join that returns a collection of matching right-side items per left-side item.

// Each patient with their list of prescriptions
var patientsWithAllRx = from p in db.Patients
                        join rx in db.Prescriptions
                          on p.Id equals rx.PatientId into rxGroup
                        select new PatientWithPrescriptionsDto(
                            p.Id,
                            p.FullName,
                            rxGroup.Select(r => r.DrugName).ToList()
                        );

// Method syntax
var result = db.Patients.GroupJoin(
    db.Prescriptions,
    p  => p.Id,
    rx => rx.PatientId,
    (patient, prescriptions) => new PatientWithPrescriptionsDto(
        patient.Id,
        patient.FullName,
        prescriptions.Select(r => r.DrugName).ToList()));

Production issue I've seen: A team used GroupJoin in EF Core expecting it to generate a single SQL JOIN. It actually generates N+1 queries — one for each patient plus one for each patient's prescriptions. Use Include with navigation properties for this pattern in EF Core, or project a flat join and restructure in memory.

Cross Join

Returns the Cartesian product — every combination of elements from both sequences.

// All combinations of drugs and administration routes
// (Useful for generating test data or coverage matrices)
var combinations = from drug  in drugs
                   from route in routes
                   select new DrugRouteMatrix(drug.Name, route.Name);

// Method syntax
var combos = drugs.SelectMany(
    d => routes,
    (d, r) => new DrugRouteMatrix(d.Name, r.Name));

In-Memory Join on Multiple Keys

// Join on multiple conditions
var matched = from order  in drugOrders
              join supply in pharmacy
                on new { order.DrugId, order.Ward }
                equals new { supply.DrugId, supply.Ward }
              select new { order.Id, supply.BatchNo };

Zip — Parallel Sequence Join

// Combine two sequences element-by-element (like a zipper)
var patients = new[] { "Smith", "Jones", "Patel" };
var mrns     = new[] { "MRN-001", "MRN-002", "MRN-003" };

var paired = patients.Zip(mrns, (name, mrn) => new { Name = name, MRN = mrn });
// { Name = "Smith", MRN = "MRN-001" }, ...

Choosing the Right Join

Navigation properties (Include):
  Best for: EF Core queries where FK relationship is modeled
  Generates: efficient JOIN SQL or split queries
  Do not use: when you only need a few columns (prefer projection)

Explicit join syntax:
  Best for: in-memory collections, non-FK joins, multi-key joins
  Use when: navigation property is not available

GroupJoin:
  Best for: hierarchical in-memory results
  Avoid in EF Core: causes N+1 queries

Left outer join (DefaultIfEmpty):
  Best for: "all X with optional Y" — patients without prescriptions still appear

Zip:
  Best for: combining two same-length parallel sequences by position

Red Flag / Green Answer

Red Flag: "We load patients and prescriptions separately then join them in memory with a nested foreach."

Two database queries plus O(n²) comparison in memory. If patients = 1,000 and prescriptions = 50,000, that's 50 million comparisons. A SQL JOIN does this in milliseconds on indexed columns.

Green Answer:

Single query with projection and join in EF Core. Or: load both lists, then use ToDictionary / ToLookup for O(1) lookup instead of nested foreach.

Key Takeaway

LINQ supports inner join (join...on...equals), left outer join (join...into...DefaultIfEmpty()), GroupJoin (hierarchical), and cross join (SelectMany with no filter). With EF Core, prefer navigation properties (Include) over explicit joins for modeled relationships — they generate better SQL. Use explicit joins for in-memory collections, multi-key joins, or relationships not modeled in EF Core. Avoid GroupJoin with EF Core — it generates N+1.