LeftJoin and RightJoin in EF Core 10

After 20 years of LINQ, .NET 10 and EF Core 10 finally introduce first-class LeftJoin and RightJoin operators. No more GroupJoin, SelectMany, and DefaultIfEmpty gymnastics.

The Problem: Left Joins Before .NET 10

If you’ve ever written a LEFT JOIN in LINQ, you know the pain. What should be simple becomes a complex dance of multiple operators:

// The old way - verbose and hard to read
var query = context.Products
    .GroupJoin(
        context.Reviews,
        product => product.Id,
        review => review.ProductId,
        (product, reviews) => new { product, reviews })
    .SelectMany(
        x => x.reviews.DefaultIfEmpty(),
        (x, review) => new
        {
            ProductName = x.product.Name,
            Rating = review != null ? review.Rating : 0
        });

This pattern is confusing, easy to get wrong, and doesn’t match the mental model of a LEFT JOIN.

The Solution: Native LeftJoin

EF Core 10 introduces LeftJoin as a first-class LINQ operator:

// The new way - clean and intuitive
var query = context.Products
    .LeftJoin(
        context.Reviews,
        product => product.Id,
        review => review.ProductId,
        (product, review) => new
        {
            ProductName = product.Name,
            Rating = review?.Rating ?? 0
        });

The intent is clear: you see LeftJoin, you know exactly what it does. The generated SQL is identical:

SELECT p.Name AS ProductName, COALESCE(r.Rating, 0) AS Rating
FROM Products AS p
LEFT JOIN Reviews AS r ON p.Id = r.ProductId

Method Signature

The LeftJoin method signature mirrors the existing Join:

public static IQueryable<TResult> LeftJoin<TOuter, TInner, TKey, TResult>(
    this IQueryable<TOuter> outer,
    IEnumerable<TInner> inner,
    Expression<Func<TOuter, TKey>> outerKeySelector,
    Expression<Func<TInner, TKey>> innerKeySelector,
    Expression<Func<TOuter, TInner?, TResult>> resultSelector)

Key difference: the TInner parameter in resultSelector is nullable (TInner?), because unmatched rows return null.

RightJoin: The Mirror Image

RightJoin keeps all rows from the second (right) collection:

var query = context.Reviews
    .RightJoin(
        context.Products,
        review => review.ProductId,
        product => product.Id,
        (review, product) => new
        {
            ProductName = product.Name,
            ReviewId = review?.Id ?? 0,
            Rating = review?.Rating ?? 0
        });

Generated SQL:

SELECT p.Name AS ProductName, 
       COALESCE(r.Id, 0) AS ReviewId,
       COALESCE(r.Rating, 0) AS Rating
FROM Reviews AS r
RIGHT JOIN Products AS p ON r.ProductId = p.Id

Practical Examples

Example 1: Customers with Optional Orders

Show all customers, even those without orders:

var customerOrders = context.Customers
    .LeftJoin(
        context.Orders,
        c => c.Id,
        o => o.CustomerId,
        (customer, order) => new
        {
            CustomerName = customer.Name,
            OrderId = order?.Id,
            OrderDate = order?.OrderDate,
            Total = order?.Total ?? 0
        });

Example 2: Products with Reviews Summary

Get all products with their average rating (including unreviewed products):

var productRatings = context.Products
    .LeftJoin(
        context.Reviews,
        p => p.Id,
        r => r.ProductId,
        (product, review) => new { product, review })
    .GroupBy(x => new { x.product.Id, x.product.Name })
    .Select(g => new
    {
        ProductId = g.Key.Id,
        ProductName = g.Key.Name,
        AverageRating = g.Average(x => x.review != null ? x.review.Rating : (double?)null),
        ReviewCount = g.Count(x => x.review != null)
    });

Example 3: Employees with Optional Department

var employeeReport = context.Employees
    .LeftJoin(
        context.Departments,
        e => e.DepartmentId,
        d => d.Id,
        (employee, department) => new
        {
            employee.FirstName,
            employee.LastName,
            DepartmentName = department?.Name ?? "[Unassigned]"
        });

Example 4: Chaining Multiple LeftJoins

var fullReport = context.Orders
    .LeftJoin(
        context.Customers,
        o => o.CustomerId,
        c => c.Id,
        (order, customer) => new { order, customer })
    .LeftJoin(
        context.Shippers,
        x => x.order.ShipperId,
        s => s.Id,
        (x, shipper) => new
        {
            OrderId = x.order.Id,
            CustomerName = x.customer?.Name ?? "[Unknown]",
            ShipperName = shipper?.Name ?? "[Not Shipped]"
        });

Handling Null Values

Since unmatched rows return null, always handle nullable types:

// Use null-conditional and null-coalescing operators
(product, review) => new
{
    ProductName = product.Name,
    ReviewText = review?.Comment ?? "No review",
    Rating = review?.Rating ?? 0,
    ReviewDate = review?.CreatedAt
}

For reference types that might be null, use appropriate defaults:

// Option 1: Null-coalescing
DepartmentName = department?.Name ?? "[None]"

// Option 2: Conditional expression
DepartmentName = department != null ? department.Name : "[None]"

Comparison: Join Types

Requirements and Limitations

Requirements:

• .NET 10 or later
• EF Core 10 or later
• Works with SQL Server, PostgreSQL, SQLite, and other relational providers

Limitations:

• Method syntax only: Query syntax (from ... join ... into) doesn’t support left join keyword yet
• Equijoins only: Keys are compared for equality (same as Join)
• No composite key syntax sugar: For multiple columns, use anonymous types

// Multiple columns join
.LeftJoin(
    context.OrderDetails,
    o => new { o.CustomerId, o.ProductId },
    d => new { d.CustomerId, d.ProductId },
    (order, detail) => ...)

Migration Guide

Migrating from the old pattern is straightforward:

// Before (.NET 9 and earlier)
var query = context.Products
    .GroupJoin(
        context.Categories,
        p => p.CategoryId,
        c => c.Id,
        (p, categories) => new { p, categories })
    .SelectMany(
        x => x.categories.DefaultIfEmpty(),
        (x, category) => new
        {
            x.p.Name,
            CategoryName = category != null ? category.Name : "Uncategorized"
        });

// After (.NET 10)
var query = context.Products
    .LeftJoin(
        context.Categories,
        p => p.CategoryId,
        c => c.Id,
        (product, category) => new
        {
            product.Name,
            CategoryName = category?.Name ?? "Uncategorized"
        });

Performance Analysis

One of the most important questions when adopting new features is: does it perform as well as raw SQL? Let’s analyse the performance of LeftJoin compared to manual SQL queries.

Generated SQL Comparison

The good news: EF Core 10’s LeftJoin generates identical SQL to what you would write manually.

// LINQ LeftJoin
var query = context.Products
    .LeftJoin(
        context.Reviews,
        p => p.Id,
        r => r.ProductId,
        (product, review) => new
        {
            product.Name,
            Rating = review != null ? review.Rating : 0
        });

Generated SQL:

SELECT p.Name, COALESCE(r.Rating, 0) AS Rating
FROM Products AS p
LEFT JOIN Reviews AS r ON p.Id = r.ProductId

This is exactly what you would write by hand. No additional subqueries, no unnecessary operations.

Execution Plan Analysis

To verify performance, we can compare execution plans. Here’s how to capture them:

// Method 1: Using ToQueryString() to see the SQL
var sql = query.ToQueryString();
Console.WriteLine(sql);

// Method 2: Enable sensitive data logging for detailed output
optionsBuilder
    .LogTo(Console.WriteLine, LogLevel.Information)
    .EnableSensitiveDataLogging();

// Method 3: Using SQL Server's execution plan
// LINQ query
var linqQuery = context.Products
    .LeftJoin(context.Reviews, p => p.Id, r => r.ProductId,
        (p, r) => new { p.Name, Rating = r != null ? r.Rating : 0 });

// Raw SQL equivalent
var rawQuery = context.Database
    .SqlQueryRaw<ProductRatingDto>(@"
        SELECT p.Name, COALESCE(r.Rating, 0) AS Rating
        FROM Products p
        LEFT JOIN Reviews r ON p.Id = r.ProductId");

Benchmark Results

Using BenchmarkDotNet, we compared three approaches:

Key findings:

1. LeftJoin vs GroupJoin: LeftJoin is marginally faster due to simpler expression tree processing
2. LeftJoin vs Raw SQL: Virtually identical - the 2% difference is within measurement error
3. Memory: LeftJoin allocates less memory than the old GroupJoin pattern

SQL Server Execution Plan

Both queries produce identical execution plans:

|--Nested Loops(Left Outer Join)
     |--Clustered Index Scan (Products)
     |--Index Seek (Reviews.IX_ProductId)

The query optimiser sees the same logical operation regardless of whether you use LeftJoin or raw SQL.

When Raw SQL Might Be Faster

There are edge cases where raw SQL could outperform LINQ:

1. Complex joins with hints: Query hints like WITH (NOLOCK) or OPTION (RECOMPILE)

   // Raw SQL with hints
   var query = context.Database.SqlQueryRaw<ResultDto>(@"
       SELECT p.Name, r.Rating
       FROM Products p WITH (NOLOCK)
       LEFT JOIN Reviews r WITH (NOLOCK) ON p.Id = r.ProductId
       OPTION (RECOMPILE)");

2. Database-specific syntax: Features not supported by LINQ

   -- SQL Server specific: CROSS APPLY with TOP
   SELECT p.Name, r.Rating
   FROM Products p
   CROSS APPLY (
       SELECT TOP 1 Rating 
       FROM Reviews 
       WHERE ProductId = p.Id 
       ORDER BY CreatedAt DESC
   ) r

3. Very complex aggregations: Multiple nested subqueries

Optimisation Tips

1. Use projections: Only select columns you need

   // ✅ Good - only selects required columns
   .LeftJoin(..., (p, r) => new { p.Name, r.Rating })
   
   // ❌ Avoid - selects entire entities
   .LeftJoin(..., (p, r) => new { Product = p, Review = r })

2. Add appropriate indexes: Ensure foreign key columns are indexed

   CREATE INDEX IX_Reviews_ProductId ON Reviews(ProductId);

3. Use AsNoTracking for read-only queries:

   var query = context.Products
       .AsNoTracking()
       .LeftJoin(context.Reviews, ...);

4. Consider compiled queries for hot paths:

   private static readonly Func<AppDbContext, int, IEnumerable<ProductDto>> 
       GetProductWithReviews = EF.CompileQuery(
           (AppDbContext ctx, int productId) =>
               ctx.Products
                   .Where(p => p.Id == productId)
                   .LeftJoin(ctx.Reviews, p => p.Id, r => r.ProductId,
                       (p, r) => new ProductDto { ... }));

Conclusion on Performance

The LeftJoin operator performs identically to raw SQL in virtually all scenarios. The SQL generated is optimal, and execution plans are equivalent. You can confidently use LeftJoin without worrying about performance penalties.

The only reasons to prefer raw SQL are:

• Database-specific features or hints
• Extremely complex queries that don’t translate well to LINQ
• Legacy systems with existing optimised queries

For 99% of use cases, LeftJoin is the right choice.

Best Practices

1. Use Navigation Properties When Possible

   If relationships are configured, EF Core handles joins automatically:

   // If navigation property exists, this is cleaner
   var query = context.Products
       .Include(p => p.Category)
       .Select(p => new
       {
           p.Name,
           CategoryName = p.Category != null ? p.Category.Name : "None"
       });

2. LeftJoin for Reporting Scenarios

   Use LeftJoin when you need to include items without related data:

   // All products, even those never sold
   var salesReport = context.Products
       .LeftJoin(
           context.OrderItems,
           p => p.Id,
           oi => oi.ProductId,
           (product, orderItem) => new { product, orderItem })
       .GroupBy(x => x.product.Name)
       .Select(g => new
       {
           ProductName = g.Key,
           TotalSold = g.Sum(x => x.orderItem != null ? x.orderItem.Quantity : 0)
       });

3. Verify Generated SQL

   Always check the SQL for complex queries:

   var sql = query.ToQueryString();
   Console.WriteLine(sql);

Conclusion

The addition of LeftJoin and RightJoin to LINQ is a quality-of-life improvement that makes code more readable and maintainable. After 20 years, we finally have join operators that match our SQL mental model.

The benefits are clear:

• Readable: The intent is obvious from the method name
• Concise: Less boilerplate code
• Familiar: Matches SQL JOIN syntax
• Type-safe: Nullable types enforce proper null handling

If you’re upgrading to .NET 10, this is one of the first features you should adopt in your EF Core queries.