LINQ Joins: The Operator You Probably Don't Need

In SQL, you write JOINs constantly. In LINQ with Entity Framework, explicit joins are often a code smell.

Let me explain why — and when you actually need them.

Navigation Properties: The EF Way

If you have proper relationships configured:

public class Order
{
    public int Id { get; set; }
    public int CustomerId { get; set; }
    public Customer Customer { get; set; }  // Navigation property
}

public class Customer
{
    public int Id { get; set; }
    public string Name { get; set; }
    public List<Order> Orders { get; set; }  // Collection navigation
}

You don't need Join. Just navigate:

// EF automatically generates the JOIN
var orderDetails = dbContext.Orders
    .Where(o => o.Customer.Name.StartsWith("A"))  // Navigate through relationship
    .Select(o => new 
    {
        o.Id,
        CustomerName = o.Customer.Name  // EF handles the JOIN
    })
    .ToList();

SQL generated:

SELECT o.Id, c.Name as CustomerName
FROM Orders o
INNER JOIN Customers c ON o.CustomerId = c.Id
WHERE c.Name LIKE 'A%'

Clean. No explicit join syntax.

When You Actually Need Join

Explicit Join makes sense when:

1. No navigation property — raw tables without relationships
2. In-memory collections — joining two lists
3. Non-key relationships — joining on arbitrary columns

Scenario 1: No Relationship Configured

var orderWithCustomer = dbContext.Orders
    .Join(
        dbContext.Customers,
        order => order.CustomerId,      // Outer key
        customer => customer.Id,        // Inner key
        (order, customer) => new        // Result selector
        {
            OrderId = order.Id,
            CustomerName = customer.Name
        }
    )
    .ToList();

The syntax is verbose — four parameters. This is why navigation properties are preferred.

Scenario 2: In-Memory Collections

var products = GetProducts();
var prices = GetPriceUpdates();

var updated = products
    .Join(
        prices,
        p => p.Sku,
        u => u.Sku,
        (product, update) => new 
        {
            product.Name,
            OldPrice = product.Price,
            NewPrice = update.Price
        }
    )
    .ToList();

For in-memory joins, there's no EF to generate SQL. The Join operator uses hash-based matching — efficient even on large collections.

Fun fact: LINQ's Join uses a hash join algorithm internally. It builds a lookup table from the inner sequence, then probes it for each outer element. Time complexity: O(n + m) instead of O(n × m) for nested loops. The same optimization that makes database joins fast.

Left Join: The GroupJoin Pattern

LINQ's Join is an inner join — no match, no result. For left joins, you need GroupJoin:

var customersWithOrders = dbContext.Customers
    .GroupJoin(
        dbContext.Orders,
        customer => customer.Id,
        order => order.CustomerId,
        (customer, orders) => new 
        {
            customer.Name,
            OrderCount = orders.Count(),
            Orders = orders.ToList()
        }
    )
    .ToList();

GroupJoin gives you each customer with a collection of their orders (possibly empty).

For the classic LEFT JOIN behavior (one row per result, null if no match):

var leftJoin = dbContext.Customers
    .GroupJoin(
        dbContext.Orders,
        c => c.Id,
        o => o.CustomerId,
        (customer, orders) => new { customer, orders }
    )
    .SelectMany(
        x => x.orders.DefaultIfEmpty(),  // Flatten, keeping nulls
        (x, order) => new 
        {
            CustomerName = x.customer.Name,
            OrderId = order != null ? order.Id : (int?)null
        }
    )
    .ToList();

Ugly, right? This is why navigation properties with .Include() are the EF way.

The Query Syntax Alternative

Join looks cleaner in query syntax:

var result = 
    from order in dbContext.Orders
    join customer in dbContext.Customers 
        on order.CustomerId equals customer.Id
    select new { order.Id, customer.Name };

// Left join in query syntax
var leftJoin = 
    from customer in dbContext.Customers
    join order in dbContext.Orders 
        on customer.Id equals order.CustomerId into orders
    from order in orders.DefaultIfEmpty()
    select new { customer.Name, OrderId = order.Id };

The into keyword creates a grouped join. The second from with DefaultIfEmpty() flattens it into a left join.

Multiple Joins

Chaining joins gets messy fast:

var result = dbContext.Orders
    .Join(dbContext.Customers, o => o.CustomerId, c => c.Id, (o, c) => new { o, c })
    .Join(dbContext.Products, oc => oc.o.ProductId, p => p.Id, (oc, p) => new 
    {
        OrderId = oc.o.Id,
        CustomerName = oc.c.Name,
        ProductName = p.Name
    })
    .ToList();

Versus navigation properties:

var result = dbContext.Orders
    .Select(o => new 
    {
        o.Id,
        CustomerName = o.Customer.Name,
        ProductName = o.Product.Name
    })
    .ToList();

Night and day.

The Rule

1. Have navigation properties? Just navigate — EF handles joins
2. In-memory collections? Join is the right tool
3. Need left join in EF? Use GroupJoin + SelectMany, or include nullable navigation
4. Complex multi-table queries? Query syntax is more readable than chained Join calls

---

Next time, we'll explore the dangerous world of N+1 queries — the silent performance killer that makes your 1-second query take 30 seconds. Hope to see you!