LINQ channel icon LINQ · 26 published articles

Using LINQ Join to combine data

In this post I will teach you how to combine data between collections using LINQ in .NET. All with live working demo.

Abdul Rahman Edit on GitHub (opens in a new tab)
Using LINQ Join to combine data
On this page

What you'll learn

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

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

When working with two collections, we can combine them using LINQ Join() method. This will join elements between two or more collections and gives a single collection. Join() also know as Equi Join or Inner Join in SQL. To work with Join(), we need atleast one property in each collection to share equal value.

Why it matters

LINQ Join() is used to answer questions about collection such as

  • Joining collections using common key value to define relationship between them
  • Joining Customer and their Orders
  • Joining Order and Order Items

Implementation

Using LINQ Join to combine primitive types

Primitive data types like int, decimal, string, etc can just combine the values against other equal values in the collection

C# 


List<int> list = new() { 1, 2, 3, 4, 5 };
List<int> anotherList = new() { 4, 5, 6 };

//Method Syntax
var result = list
             .Join(anotherList, 
                   number => number, 
                   anotherNumber => anotherNumber, 
                   (number, anotherNumber) => new { number, anotherNumber ])
             .ToList();
                    
//Query Syntax
var result = (from number in list
             join anotherNumber in anotherList 
             on number equals anotherNumber
             select new { number, anotherNumber })
             .ToList();
Live demo: LINQ Join Clause Demo — interactive controls are available when the page loads.

Using LINQ Join to combine Collections using single field

We can use Join() to combine elements between two or more sequences (arrays, lists, etc.) based on a key value. The result is a new sequence that contains elements with the matching key and their associated values.

C# 


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

//Method Syntax
List<ProductSale> result = products
                           .Join(sales, 
                                 product => product.Id,
                                 sale => sale.ProductId,
                                 (product, sale) => new ProductSale
                                 {
                                    ProductId = product.Id,
                                    SaleId = sale.Id,
                                    Name = product.Name,
                                    Price = sale.Price
                                 })
                           .ToList();
                    
//Query Syntax
List<ProductSale> result = (from product in products
                            join sale in sales 
                            on product.Id equals sale.ProductId
                            select new ProductSale
                             {
                                ProductId = product.Id,
                                SaleId = sale.Id,
                                Name = product.Name,
                                Price = sale.Price
                             }) 
                           .ToList();
Live demo: LINQ Join Clause with Objects Demo — interactive controls are available when the page loads.

Using LINQ Join to combine Collections using multiple fields

So for joining collections with more than one field, the key selector condition will have an anonymous object containing multiple fields to compare.

C# 


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

//Method Syntax
List<ProductSale> result = products
                           .Join(sales, 
                                 product => new { ProductId = product.Id, ProductColor = product.Color },
                                 sale => new { ProductId = sale.ProductId, ProductColor = sale.ProductColor },
                                 (product, sale) => new ProductSale
                                 {
                                    ProductId = product.Id,
                                    SaleId = sale.Id,
                                    Name = product.Name,
                                    Price = sale.Price,
                                    Color = product.Color
                                 })
                           .ToList();
                    
//Query Syntax
List<ProductSale> result = (from product in products
                            join sale in sales 
                            on 
                              new { ProductId = product.Id, ProductColor = product.Color } 
                            equals 
                              new { ProductId = sale.ProductId, ProductColor = sale.ProductColor }
                            select new ProductSale
                             {
                                ProductId = product.Id,
                                SaleId = sale.Id,
                                Name = product.Name,
                                Price = sale.Price,
                                Color = product.Color
                             }) 
                           .ToList();
Live demo: LINQ Join Clause with Objects Demo — interactive controls are available when the page loads.

Summary

In this article we learn't how to combine data between collections using Join. This can be used to combine items between collection and return a new collection. This can be done using the key selector. We can have single key selector or multiple key selector as anonymous object. All these can be used with any IEnumerable or IQueryable types.