I ❤️ .NET

Table of Contents

1. What we gonna do?
2. Why we gonna do?
3. How we gonna do?
4. Summary

What we gonna do?

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 we gonna do?

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.

How we gonna do?

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.