How LeftJoin and RightJoin Work in EF Core .NET 10

Entity Framework Core has just fixed one of its biggest limitations in .NET 10.

Example

For this example, we are going to have a Product table that has an optional join on the CategoryId field against the Category table's Id field.

Product

• Id
• Name
• CategoryId

Category

• Id
• Name

We have added these as entities to an ASP.NET Core Web API, with a Category navigation property on the Product entity:

public class Product
{
	public int Id { get; set; }

	public string Name { get; set; } = string.Empty;

	public int? CategoryId { get; set; }

	public Category? Category { get; set; }
}

public class Category
{
	public int Id { get; set; }

	public string Name { get; set; } = string.Empty;
}

We have added a DbSet for both Products and Categories in the DbContext:

public class EFCoreDbContext : DbContext
{
	public required DbSet<Product> Products { get; set; }

	public required DbSet<Category> Categories { get; set; }

	public EFCoreDbContext(DbContextOptions<EFCoreDbContext> options) : base(options)
	{
	}
}

Before .NET 10

If you have a simple join and you are using navigation properties, you can use the Include method.

public async Task<ProductWithCategoryDto?> GetWithCategories(int id)
{
	return await _dbContext.Products
		.Include(p => p.Category)
		.Where(p => p.Id == id)
		.Select(p => new ProductWithCategoryDto(
			p.Id,
			p.Name,
			p.Category != null ? p.Category.Id : null,
			p.Category != null ? p.Category.Name : null
		))
		.FirstOrDefaultAsync();
}

However, if your join was more complex, you had to use the GroupJoin method. Here, you needed to supply the Categories DbSet from the DbContext, specify the join properties, then call SelectMany and DefaultIfEmpty to create a left join. You could then select the type you wished to return.

public async Task<ProductWithCategoryDto?> GetWithCategories(int id)
{
	return await _dbContext.Products
		.GroupJoin(
			_dbContext.Categories,
			p => p.CategoryId,
			c => c.Id,
			(product, category) => new { Product = product, Category = category }
		)
		.Where(p => p.Product.Id == id)
		.SelectMany(
			join => join.Category.DefaultIfEmpty(),
			(join, category) => new ProductWithCategoryDto(
				join.Product.Id,
				join.Product.Name,
				category != null ? category.Id : null,
				category != null ? category.Name : null
			)
		)
		.FirstOrDefaultAsync();
}

Introducing LeftJoin and RightJoin

.NET 10 introduces the new LeftJoin and RightJoin methods when using LINQ. With the LeftJoin method, you supply the DbSet for the table you are joining, the two properties to join on, and what is being returned.

public async Task<ProductWithCategoryDto?> GetWithCategories(int id)
{
	return await _dbContext.Products
		.Where(p => p.Id == id)
		.LeftJoin(
			_dbContext.Categories,
			product => product.CategoryId,
			category => category.Id,
			(product, category) => new ProductWithCategoryDto(
				product.Id,
				product.Name,
				category != null ? category.Id : null,
				category != null ? category.Name : null
			)
		)
		.FirstOrDefaultAsync();
}

A right join is the opposite of a left join, where you query the second table and then join the first table to it.

public async Task<IList<CategoryWithProductDto>> GetWithProducts(int id)
{
	return await _dbContext.Categories
		.Where(c => c.Id == id)
		.RightJoin(
			_dbContext.Products,
			category => category.Id,
			product => product.CategoryId,
			(product, category) => new CategoryWithProductDto(
				category.Id,
				category.Name,
				product != null ? product.Id : null,
				product != null ? product.Name : null
			)
		)
		.ToListAsync();
}

Does it work with query syntax?

No, unfortunately it does not. You still have to use DefaultIfEmpty when using query syntax:

public async Task<ProductWithCategoryDto?> GetWithCategories(int id)
{
	return await (
		from p in _dbContext.Products
		join c in _dbContext.Categories
			on p.CategoryId equals c.Id into pc
		from c in pc.DefaultIfEmpty() // LEFT JOIN
		where p.Id == id
		select new ProductWithCategoryDto(
			p.Id,
			p.Name,
			c != null ? c.Id : (int?)null,
			c != null ? c.Name : null
		)
	).FirstOrDefaultAsync();
}

Using Entity Framework Core in a real-world API

If you want to use Entity Framework Core in a real-world API, then our Minimal APIs for complete beginners course is just for you. You will learn how to query, insert, update, and delete records from the database, and the best way to use them in a Web API.

Watch the video

Watch this video where I go through the old ways of doing joins and show how you can use LeftJoin and RightJoin in a Web API project:

Final thoughts

It is good that Microsoft have added LeftJoin and RightJoin to LINQ. Going forward, we hope they also add support for these methods in query syntax. However, this change already makes writing complex joins in Entity Framework Core much simpler.