Simulating Left Outer Join using LINQ

What you'll learn

In this article, let's learn about how to do Left Outer Join in LINQ in .NET.

Note: If you have not done so already, I recommend you read the article on Using LINQ Group Join to combine data.

A LEFT OUTER JOIN in SQL returns all the rows from the left table, and the matching rows from the right table. If there is no match, the result will contain NULL values for the right table's columns.

Why it matters

It is needed when you want to return all the records from one table the left table and any matching records from another table the right table. This is useful when you want to include records from one table that do not have matching records in the other table.

Implementation

Simulating Left Outer Join using LINQ

Simulating Left Outer Join can be done using both Method Syntax and Query Syntax.

With Method Syntax we can use,

1. SelectMany() method to select the 'right' collection.
2. Where() method to filter the 'right' collection.
3. DefaultIfEmpty() for 'right' collection.

With Query Syntax we can use,

1. inner join using into and a second from statement.
2. null object may be returned in 'right' collection.
3. DefaultIfEmpty() for 'right' collection.

List<Product> products = GetProducts();
List<Sale> sales = GetSales();
List<ProductSale> result = new();

//Method Syntax
List<ProductSales> result = products
                           .SelectMany(product => sales.Where(sale => sale.ProductId == product.Id).DefaultIfEmpty(),
                           (product, sale) => new ProductSale
                           {
                              SaleId = sale?.Id,
                              ProductId = product.Id,
                              Name = product.Name,
                              Price = sale?.Price,
                              Color = sale?.ProductColor
                           })
                           .ToList();
                    
//Query Syntax
List<ProductSales> result = (from product in products
                            join sale in sales 
                            on product.Id equals sale.ProductId
                            into productSales
                            from sale in productSales.DefaultIfEmpty()
                            select new ProductSale
                             {
                                SaleId = sale?.Id,
                                ProductId = product.Id,
                                Name = product.Name,
                                Price = sale?.Price,
                                Color = sale?.ProductColor
                             }) 
                           .ToList();
        

Summary

In this article we learn't how to combine data between collections by doing a Left Outer Join using LINQ. This allows you to group elements from one collection with elements from another collection based on a specified key representing one-to-many relationship. All these can be used with any IEnumerable or IQueryable types.