Cleaner Joins in EF Core 10 with LeftJoin and RightJoin

Remember the first time you tried to express a left outer join in LINQ and immediately forgot the DefaultIfEmpty incantation? Same. I used to keep a snippet file named please-do-the-left-join-thing.txt. With EF Core 10, we finally get clear, readable outer joins with LeftJoin and RightJoin. Let’s walk through what changed, why it matters, and how to avoid those sneaky null traps.

permalinkWhy outer joins matter in real apps

Outer joins power everyday scenarios like showing all employees even when they have no orders, or listing all products including those that have not sold yet. The shape of the data should be accurate, not aspirational. If someone named Dwight did not sell any paper today, the dashboard should still show Dwight.

permalinkThe old way in EF Core

For years, a left outer join meant using GroupJoin plus SelectMany with DefaultIfEmpty. It worked, but it was less than friendly to the next developer who read it.

Here is the classic pattern many of us wrote by muscle memory:

Before we code, a quick reminder. The key pieces are:

• Group the inner set by a matching key.
• Flatten with SelectMany.
• Use DefaultIfEmpty to keep outer rows.

var employeeOrders = await ctx.Employees
    .GroupJoin(ctx.PaperOrders,
        e => e.Id,
        o => o.EmployeeId,
        (e, orders) => new { e, orders })
    .SelectMany(x => x.orders.DefaultIfEmpty(),
        (x, o) => new { Employee = x.e, Order = o })
    .ToListAsync();

This is correct, but it is not exactly readable on a Monday morning.

permalinkEF Core 10 to the rescue

EF Core 10 introduces LeftJoin and RightJoin. The goal is clarity. You keep all rows from one side without remembering the GroupJoin dance.

Let’s keep all employees, matched to any orders if they exist. If no order is found, o is null. That means Dwight can show up orderless and still feel seen.

var employeeOrders = await ctx.Employees.LeftJoin(
    ctx.PaperOrders,
    e => e.Id,
    o => o.EmployeeId,
    (e, o) => new { e.Name, OrderId = o == null ? 0 : o.Id })
    .ToListAsync();

Under the hood, EF Core translates this to a SQL LEFT JOIN where supported.

permalinkWhat about RightJoin

RightJoin keeps all rows from the right sequence. In many cases this is equivalent to swapping the sequences in a left join. Providers that support RIGHT JOIN may emit it. Others may rewrite the join.

Here is an example that keeps every employee even if there is no matching order, but expressed using RightJoin:

var employeeOrders = await ctx.PaperOrders.RightJoin(
    ctx.Employees,
    o => o.EmployeeId,
    e => e.Id,
    (o, e) => new { e.Name, OrderId = o == null ? 0 : o.Id })
    .ToListAsync();

If your brain prefers reading from the perspective of the preserved table, pick the join that matches that perspective.

permalinkNull handling without surprises

With outer joins, the inner side may be missing. Use null checks or the null conditional operator to avoid exceptions. Think of it as wearing a seatbelt before taking the query out for a spin.

Here is a safe projection that handles a missing order:

var view = await ctx.Employees.LeftJoin(
    ctx.PaperOrders,
    e => e.Id,
    o => o.EmployeeId,
    (e, o) => new { e.Name, Amount = o?.Total ?? 0m })
    .ToListAsync();

permalinkFiltering with outer joins without breaking semantics

Applying Where after a left join can accidentally turn it into an inner join if you filter on the inner side without a null check. To target only employees who have no orders, explicitly check for null.

var employeesWithNoOrders = await ctx.Employees.LeftJoin(
    ctx.PaperOrders,
    e => e.Id,
    o => o.EmployeeId,
    (e, o) => new { e, o })
    .Where(x => x.o == null)
    .Select(x => x.e.Name)
    .ToListAsync();

To filter employees by department while keeping all employees, filter the outer side either before the join or with an outer-side predicate after the join.

var salesTeam = await ctx.Employees
    .Where(e => e.Department == "Sales")
    .LeftJoin(ctx.PaperOrders,
        e => e.Id,
        o => o.EmployeeId,
        (e, o) => new { e.Name, o })
    .ToListAsync();

permalinkComposite keys are welcome

When your relationship uses multiple columns, select a new anonymous key on both sides. Think of it as matching on both employee and branch.

var joined = await ctx.Employees.LeftJoin(
    ctx.PaperOrders,
    e => new { e.Id, e.BranchId },
    o => new { Id = o.EmployeeId, o.BranchId },
    (e, o) => new { e.Name, o?.Id })
    .ToListAsync();

permalinkAggregates after a left join

Outer joins and aggregates go together like pretzels and mustard. Count orders per employee, including zero for those with none.

var counts = await ctx.Employees.LeftJoin(
    ctx.PaperOrders,
    e => e.Id,
    o => o.EmployeeId,
    (e, o) => new { e.Name, HasOrder = o != null })
    .GroupBy(x => x.Name)
    .Select(g => new { Name = g.Key, Orders = g.Count(x => x.HasOrder) })
    .ToListAsync();

permalinkProvider behaviors and practical tips

A few quick points to keep your queries fast and portable:

• Some providers have limited or no native support for RIGHT JOIN. EF Core may rewrite or emulate as needed.
• Only project the columns you need. Your SELECT list is your friend.
• Prefer AsNoTracking for read-only queries.
• Put the preserved table on the side of the join that matches your intent. Readability matters when future you is reviewing this code.

Example with a lean projection and no tracking:

var dashboard = await ctx.Employees.AsNoTracking().LeftJoin(
    ctx.PaperOrders.AsNoTracking(),
    e => e.Id,
    o => o.EmployeeId,
    (e, o) => new { e.Name, o?.Total })
    .ToListAsync();

permalinkTiny demo models used in the examples

For completeness, here are minimal models you can adapt. Keep your real models richer, but focused.

public class Employee
{
    public int Id { get; set; }
    public string Name { get; set; } = string.Empty;
    public string Department { get; set; } = "";
    public int BranchId { get; set; }
}

public class PaperOrder
{
    public int Id { get; set; }
    public int EmployeeId { get; set; }
    public int BranchId { get; set; }
    public decimal Total { get; set; }
}

permalinkWrap up

EF Core 10 gives us outer joins that read like they behave. LeftJoin and RightJoin make intent clear, reduce bug-friendly null handling mistakes, and translate cleanly to SQL where supported. Use them to simplify your queries, keep your dashboards honest, and give poor Dwight his well deserved spot on the leaderboard, orders or not.