EF Core 10.0: Simplified LeftJoin and RightJoin

In this post, let's see a nice feature that is already available with EF Core 10.0 Preview.

For an example, consider the following DbContext Entities.

public record Customer
{
    public int Id { get; set; }
 
    public string Name { get; set; }
}
 
public record Order
{
    public int Id { get; set; }
 
    public string OrderNumber { get; set; }
 
    public Customer Customer { get; set; }
 
    public int CustomerId { get; set; }
}

Here, a Customer can have one or more Orders, or they even not have any Orders. Say we want to get all the Customers  and their Orders (if any).

So I want to write a LEFT JOIN query. Prior to EF Core 10.0, to write a LEFT JOIN, we needed to write a complex LINQ query, something like the following.

var query = context.Customers
    .GroupJoin(
        context.Orders,
        customer => customer.Id,
        order => order.CustomerId,
        (customer, orders) => new
        {
            Customer = customer,
            Orders = orders
        })
    .SelectMany(
        x => x.Orders.DefaultIfEmpty(),
        (customer, order) => new
        {
            CustomerName = customer.Customer.Name,
            OrderNumber = order.OrderNumber ?? "N/A"
        });
 
foreach (var item in query)
{
    Console.WriteLine($"{item.CustomerName,-20} {item.OrderNumber}");
}

This would generate a SQL Query as follows:

SELECT [c].[Name] AS [CustomerName], COALESCE([o].[OrderNumber], N'N/A') AS [OrderNumber]
FROM [Customers] AS [c]
LEFT JOIN [Orders] AS [o] ON [c].[Id] = [o].[CustomerId]

The output would be:

Output

And with EF Core 10.0, we can rewrite the same query as follows:

var query = context.Customers
    .LeftJoin(
        context.Orders,
        customer => customer.Id,
        order => order.CustomerId,
        (customer, order) => new
        {
            CustomerName = customer.Name,
            OrderNumber = order.OrderNumber ?? "N/A"
        });

If you run this with an EF Core version prior to 10.0, you will be getting a could not be translated error). But with EF Core 10.0, this would generate the same SQL query as before and will give the same output. The same would be applicable for RIGHT JOIN.

That's neat.

Happy Coding,

Regards,
Jaliya