Linq Join with Multiple Data Sources

Back to: CSharp LINQ and Lambda Expressions Tutorial

Linq (Language-Integrated Query) is a powerful language feature in C# that allows developers to query various data sources such as lists, arrays, databases, and XML documents. One common operation when working with data is joining data from multiple sources based on a common field. In this lesson, we will learn how to perform joins with multiple data sources using Linq in C#, and how to manipulate and analyze the resulting data.

To perform a join with multiple data sources using Linq, we first need to define the data sources and the fields that we want to join on. For example, let’s say we have two collections: a list of customers and a list of orders. We want to join these two collections based on the common “CustomerId” field. Here’s how we can do it using Linq:

var customers = new List<Customer> { ... };
var orders = new List<Order> { ... };

var query = from customer in customers
            join order in orders on customer.CustomerId equals order.CustomerId
            select new { Customer = customer, Order = order };

In this example, we use the “join” keyword to perform the join operation, and the “equals” keyword to specify the fields that we want to join on. We also use the “select” keyword to create a new object that contains both the customer and order objects.

But what if we have multiple data sources that we want to join? For example, let’s say we also have a list of products that we want to include in our query. Here’s how we can modify our query to include the products:

var customers = new List<Customer> { ... };
var orders = new List<Order> { ... };
var products = new List<Product> { ... };

var query = from customer in customers
            join order in orders on customer.CustomerId equals order.CustomerId
            join product in products on order.ProductId equals product.ProductId
            select new { Customer = customer, Order = order, Product = product };

In this modified query, we use two “join” keywords to join the orders and products collections to the customers collection. We specify the join condition for each join using the “equals” keyword, and we create a new object that contains all three objects (customer, order, and product).

We can also perform left and right outer joins using Linq, by using the “into” keyword and the “DefaultIfEmpty” method. For example, here’s how we can perform a left outer join between customers and orders:

var query = from customer in customers
            join order in orders on customer.CustomerId equals order.CustomerId into customerOrders
            from order in customerOrders.DefaultIfEmpty()
            select new { Customer = customer, Order = order };

In this example, we use the “into” keyword to group the orders by customer, and the “DefaultIfEmpty” method to include customers that do not have any orders. We then use the “from” keyword to flatten the resulting collection, and create a new object that contains both the customer and order objects.

In conclusion, performing joins with multiple data sources using Linq in C# is a powerful technique that can help you manipulate and analyze complex data sets. By understanding the syntax and usage of the “join” keyword, as well as other Linq operators such as “select” and “DefaultIfEmpty”, you can create queries that join data from multiple sources and provide meaningful insights into your data.