Back to: LINQ Tutorial For Beginners and Professionals
LINQ Join with Multiple Data Sources in C#
In this article, I will discuss How to Perform LINQ Inner Join with Multiple Data Sources in C# with Examples. Before proceeding to this article, I strongly recommend you read our previous article, where we discussed how to perform LINQ Inner Join with two Data Sources in C# with Examples using Method and Query Syntax.
LINQ Join with Multiple Data Sources in C#
In C#, you can perform a LINQ Join with multiple data sources by using the join keyword, and the equals keyword in combination with the on keyword. This allows you to combine data from multiple collections or sources based on a common key or condition. Here’s how you can do it:
Assuming you have three data sources (collection1, collection2, and collection3) that you want to join and you want to perform an inner join, the basic syntax for a LINQ join with multiple data sources is as follows:
In this Example:
- collection1, collection2, and collection3 represent your data sources or collections.
- Key1 is the common key used to perform the joins. You can use any key or condition that matches your data.
- Property1, Property2, and Property3 represent the properties you want to include in the result.
Here’s a step-by-step breakdown of the process:
- Start with the first collection (collection1 in this example).
- Use the join keyword followed by the second collection (collection2) and the on keyword to specify the common key (Key1) between the two collections.
- Add additional join clauses to join more collections if needed. Each subsequent join clause specifies the collection to join and the common key.
- In the select clause, create a new object that combines properties from all joined collections. You can include properties from each collection as needed.
- The result of this query will be an IEnumerable of the anonymous type specified in the select clause, containing data from all the joined collections.
Examples to Understand LINQ Join with Multiple Data Sources in C#:
We will use the following three data sources, i.e., Employee, Address, and Department data sources. Here, the AddressId property of the Employee Data Source points to the ID property of the Address Data Source. Similarly, the DepartmentId property of the Employee Data Source refers to the ID property of the Department Data Source. That means there are some common properties between these three data sources, and using those properties, we will perform the Join. The point that you need to remember is to perform the Join Operation, and we need to have a common property.
Our business requirement is to fetch the following data from the above three data sources. Notice we are only required to fetch the data which exists in all three data sources. We need to ID and Name property from the Employee Data Source, Department property from the Department Data Source, and AddressLine Property value as Address from the Address Data Source.
As you can see, we need to fetch only the employees with employee IDs 1, 2, 3, 9, 10, and 11. This is because these 6 employees are present in all the data sources, and while we are performing the LINQ Inner join, it only fetches the records that exist in both data sources.
Creating the Model Classes and Data Sources:
So, create a class file named Employee.cs and copy and paste the following code. This class has four properties, i.e., Id, Name, AddressId, and DepartmentId. We have also created one method to return a collection of Employees, which is going to one of our data sources.
using System.Collections.Generic;
namespace LINQJoin
{
public class Employee
{
public int ID { get; set; }
public string Name { get; set; }
public int AddressId { get; set; }
public int DepartmentId { get; set; }
public static List<Employee> GetAllEmployees()
{
return new List<Employee>()
{
new Employee { ID = 1, Name = "Preety", AddressId = 1, DepartmentId = 10 },
new Employee { ID = 2, Name = "Priyanka", AddressId = 2, DepartmentId =20 },
new Employee { ID = 3, Name = "Anurag", AddressId = 3, DepartmentId = 30 },
new Employee { ID = 4, Name = "Pranaya", AddressId = 4, DepartmentId = 0 },
new Employee { ID = 5, Name = "Hina", AddressId = 5, DepartmentId = 0 },
new Employee { ID = 6, Name = "Sambit", AddressId = 6, DepartmentId = 0 },
new Employee { ID = 7, Name = "Happy", AddressId = 7, DepartmentId = 0 },
new Employee { ID = 8, Name = "Tarun", AddressId = 8, DepartmentId = 0 },
new Employee { ID = 9, Name = "Santosh", AddressId = 9, DepartmentId = 10 },
new Employee { ID = 10, Name = "Raja", AddressId = 10, DepartmentId = 20 },
new Employee { ID = 11, Name = "Ramesh", AddressId = 11, DepartmentId = 30 }
};
}
}
}
Next, create another class file with the name Address.cs and copy and paste the following code. This class has 2 properties, i.e., Id and AddressLine. We have also created one method to return a collection of addresses, which will be the second data source for the Inner Join.
using System.Collections.Generic;
namespace LINQJoin
{
public class Address
{
public int ID { get; set; }
public string AddressLine { get; set; }
public static List<Address> GetAllAddresses()
{
return new List<Address>()
{
new Address { ID = 1, AddressLine = "AddressLine1" },
new Address { ID = 2, AddressLine = "AddressLine2" },
new Address { ID = 3, AddressLine = "AddressLine3" },
new Address { ID = 4, AddressLine = "AddressLine4" },
new Address { ID = 5, AddressLine = "AddressLine5" },
new Address { ID = 9, AddressLine = "AddressLine9" },
new Address { ID = 10, AddressLine = "AddressLine10"},
new Address { ID = 11, AddressLine = "AddressLine11"},
};
}
}
}
Finally, create another class file with the name Department.cs and then copy and paste the following code into it. This class has 2 properties, i.e., ID and Name. We have also created one method to return a collection of departments, which will be the third data source.
using System.Collections.Generic;
namespace LINQJoin
{
public class Department
{
public int ID { get; set; }
public string Name { get; set; }
public static List<Department> GetAllDepartments()
{
return new List<Department>()
{
new Department { ID = 10, Name = "IT" },
new Department { ID = 20, Name = "HR" },
new Department { ID = 30, Name = "Payroll" },
};
}
}
}
Joining three Data Sources using LINQ Query Syntax in C#:
The following code snippet shows how to join three data sources using Linq Query Syntax in C#. This is very much similar to SQL Join. Here, first, we are fetching the employee’s collection using the emp variable and then joining it with the addresses collection using the join operator (with the joining condition as on emp.AddressId equals adrs.ID). We are accessing the addresses collection using the adrs variable. Then again, we are joining the employee collection with the department collection using the join operator (with the joining condition as on emp.DepartmentId equals dept.ID). We are accessing the department collection using the dept variable. And finally, projecting the result into an anonymous type. Here, we are fetching ID and EmployeeName properties from the Employees collection, DepartmentName from the Department collection, and AddressLine from the Address Collection.
If you want to join the fourth data source, then you need to write another join within the query. The complete example code is given below. You can access the query result using a for each loop, which is shown in the below example. The following example code is self-explained, so please go through the comment lines.
using System.Linq;
using System;
namespace LINQJoin
{
class Program
{
static void Main(string[] args)
{
var JoinMultipleDSUsingQS = (//Data Source1 i.e. Employee
from emp in Employee.GetAllEmployees()
//Joining with Address Data Source (Data Source2)
join adrs in Address.GetAllAddresses()
on emp.AddressId equals adrs.ID
//Joining with Department Data Source (Data Source3)
join dept in Department.GetAllDepartments()
on emp.DepartmentId equals dept.ID
//Projecting the Result to an Annonymous Type
select new
{
ID = emp.ID,
EmployeeName = emp.Name,
DepartmentName = dept.Name,
AddressLine = adrs.AddressLine
}).ToList();
//Accessing the Result using a Foreach Loop
foreach (var employee in JoinMultipleDSUsingQS)
{
Console.WriteLine($"ID = {employee.ID}, Name = {employee.EmployeeName}, Department = {employee.DepartmentName}, Addres = {employee.AddressLine}");
}
Console.ReadLine();
}
}
}
Output:
Instead of projecting the result to an anonymous type, can we project the result to a named type? Yes, it is possible. Let us see how we can do this. First, create a class file with EmployeeResult.cs with the required properties you want in the result set. We have created the class with the following four properties per our requirements.
namespace LINQJoin
{
class EmployeeResult
{
public int ID { get; set; }
public string EmployeeName { get; set; }
public string DepartmentName { get; set; }
public string AddressLine { get; set; }
}
}
Next, modify the Main method of the Program class as follows. Here, you can see we are projecting the result to the above-created EmployeeResult type.
using System.Linq;
using System;
namespace LINQJoin
{
class Program
{
static void Main(string[] args)
{
var JoinMultipleDSUsingQS = (//Data Source1 i.e. Employee
from emp in Employee.GetAllEmployees()
//Joining with Address Data Source (Data Source2)
join adrs in Address.GetAllAddresses()
on emp.AddressId equals adrs.ID
//Joining with Department Data Source (Data Source3)
join dept in Department.GetAllDepartments()
on emp.DepartmentId equals dept.ID
//Projecting the Result to a Named Type i.e. EmployeeResult
select new EmployeeResult
{
ID = emp.ID,
EmployeeName = emp.Name,
DepartmentName = dept.Name,
AddressLine = adrs.AddressLine
}).ToList();
//Accessing the Result using a Foreach Loop
foreach (var employee in JoinMultipleDSUsingQS)
{
Console.WriteLine($"ID = {employee.ID}, Name = {employee.EmployeeName}, Department = {employee.DepartmentName}, Addres = {employee.AddressLine}");
}
Console.ReadLine();
}
}
}
With the above changes in place, run the application code, and you will also get the same output as the previous example, as shown in the below image.
LINQ Method Syntax to Perform Inner Join using Multiple Data Sources in C#:
Let’s see how to rewrite the previous example using LINQ Method Syntax to Join three data sources using C#. Using LINQ Method Syntax, writing the join query when joining more than two data sources is difficult. The following code snippet shows how to write the query to fetch data from three data sources using the LINQ Method syntax.
The complete example code is given below. In the example below, we use the LINQ Method Syntax to perform Inner Join between three data sources. The following example code is self-explained, so please go through the comment lines.
using System.Linq;
using System;
namespace LINQJoin
{
class Program
{
static void Main(string[] args)
{
var JoinMultipleDSUsingMS =
//Employee data Source (i.e. Data Source 1)
Employee.GetAllEmployees()
//Joining with Address data Source (i.e. Data Source 2)
.Join(
Address.GetAllAddresses(), //Inner Data Source 1
empLevel1 => empLevel1.AddressId, //Outer Key selector
addLevel1 => addLevel1.ID, //Inner Key selector
//Result set
(empLevel1, addLevel1) => new { empLevel1, addLevel1 }
)
// Joinging with Department Data Source (i.e. data Source 3)
.Join(
Department.GetAllDepartments(), //Inner Data Source 2
//You cannot access the outer key selector directly
//You can only access with the result set created in previous step
//i.e. using empLevel1 and addLevel1
empLevel2 => empLevel2.empLevel1.DepartmentId, //Outer Key selector
deptLevel1 => deptLevel1.ID, //Inner Key selector
//Result set
(empLevel2, deptLevel1) => new { empLevel2, deptLevel1 }
)
//Creating the actual result set
.Select(e => new
{
ID = e.empLevel2.empLevel1.ID,
EmployeeName = e.empLevel2.empLevel1.Name,
AddressLine = e.empLevel2.addLevel1.AddressLine,
DepartmentName = e.deptLevel1.Name
}).ToList();
foreach (var employee in JoinMultipleDSUsingMS)
{
Console.WriteLine($"ID = {employee.ID}, Name = {employee.EmployeeName}, Department = {employee.DepartmentName}, Addres = {employee.AddressLine}");
}
Console.ReadLine();
}
}
}
It will give you the same output as the previous example, as shown in the below image.
We have projected the result to an anonymous type in the above example. Instead of projecting the Result to an Anonymous Type, we can use the LINQ Method Syntax to project the result to a named type. We have already created a type called EmployeeResult. Let us project the result to the EmployeeResult type. So, modify the Main method of the Program class as follows.
using System.Linq;
using System;
namespace LINQJoin
{
class Program
{
static void Main(string[] args)
{
var JoinMultipleDSUsingMS =
//Employee data Source (i.e. Data Source 1)
Employee.GetAllEmployees()
//Joining with Address data Source (i.e. Data Source 2)
.Join(
Address.GetAllAddresses(), //Inner Data Source 1
empLevel1 => empLevel1.AddressId, //Outer Key selector
addLevel1 => addLevel1.ID, //Inner Key selector
//Result set
(empLevel1, addLevel1) => new { empLevel1, addLevel1 }
)
// Joinging with Department Data Source (i.e. data Source 3)
.Join(
Department.GetAllDepartments(), //Inner Data Source 2
//You cannot access the outer key selector directly
//You can only access with the result set created in previous step
//i.e. using empLevel1 and addLevel1
empLevel2 => empLevel2.empLevel1.DepartmentId, //Outer Key selector
deptLevel1 => deptLevel1.ID, //Inner Key selector
//Result set
(empLevel2, deptLevel1) => new { empLevel2, deptLevel1 }
)
//Creating the actual result set
.Select(e => new EmployeeResult
{
ID = e.empLevel2.empLevel1.ID,
EmployeeName = e.empLevel2.empLevel1.Name,
AddressLine = e.empLevel2.addLevel1.AddressLine,
DepartmentName = e.deptLevel1.Name
}).ToList();
foreach (var employee in JoinMultipleDSUsingMS)
{
Console.WriteLine($"ID = {employee.ID}, Name = {employee.EmployeeName}, Department = {employee.DepartmentName}, Addres = {employee.AddressLine}");
}
Console.ReadLine();
}
}
}
Run the application, and you will get the same output as the previous example.
Real-Time Example to Understand LINQ Join Based on 3 Data Sources:
The following example demonstrates how to join three collections: customers, orders, and orderDetails and project the result into a new object that includes the customer’s name, order date, and product name. You can choose to use either query syntax or method syntax based on which is more readable or convenient for your specific scenario.
using System;
using System.Linq;
namespace LINQDemo
{
public class Program
{
static void Main()
{
// Define the data sources
var customers = new[] {
new Customer { CustomerId = 1, Name = "Customer A" },
new Customer { CustomerId = 2, Name = "Customer B" }
};
var orders = new[] {
new Order { OrderId = 1, CustomerId = 1, OrderDate = DateTime.Now },
new Order { OrderId = 2, CustomerId = 2, OrderDate = DateTime.Now }
};
var orderDetails = new[] {
new OrderDetails { OrderDetailId = 1, OrderId = 1, ProductName = "Product 1" },
new OrderDetails { OrderDetailId = 2, OrderId = 2, ProductName = "Product 2" }
};
// Perform the join using query syntax
var querySyntax = from customer in customers
join order in orders on customer.CustomerId equals order.CustomerId
join detail in orderDetails on order.OrderId equals detail.OrderId
select new
{
customer.Name,
order.OrderDate,
detail.ProductName
};
// Perform the join using method syntax
var methodSyntax = customers
.Join(orders,
customer => customer.CustomerId,
order => order.CustomerId,
(customer, order) => new { customer, order })
.Join(orderDetails,
co => co.order.OrderId,
detail => detail.OrderId,
(co, detail) => new {
co.customer.Name,
co.order.OrderDate,
detail.ProductName
});
// Execute the query and print the results
foreach (var result in querySyntax) // or methodSyntax
{
Console.WriteLine($"{result.Name} placed an order on {result.OrderDate} for {result.ProductName}");
}
Console.ReadKey();
}
}
public class Order
{
public int OrderId { get; set; }
public DateTime OrderDate { get; set; }
public int CustomerId { get; set; }
}
public class Customer
{
public int CustomerId { get; set; }
public string Name { get; set; }
}
public class OrderDetails
{
public int OrderDetailId { get; set; }
public int OrderId { get; set; }
public string ProductName { get; set; }
}
}
You can extend this pattern to join multiple data sources as needed, as long as you have common keys or conditions to perform the joins.
My Personal Opinion: If you want to fetch the data from two data sources, you can use either the Method syntax or Query Syntax. But if you want to fetch the data from more than two data sources, it is easier to use the Query syntax than the Method syntax. But there are no performance differences whether you use the Method Syntax or Query Syntax.
In the next article, I will discuss the LINQ Group Join in C# with Examples. In this article, I try to explain How to Perform LINQ Inner Join with Multiple Data Sources using C# with Examples. I hope you understand joining multiple data sources using LINQ in C# with Examples.
About the Author: Pranaya Rout
Pranaya Rout has published more than 3,000 articles in his 11-year career. Pranaya Rout has very good experience with Microsoft Technologies, Including C#, VB, ASP.NET MVC, ASP.NET Web API, EF, EF Core, ADO.NET, LINQ, SQL Server, MYSQL, Oracle, ASP.NET Core, Cloud Computing, Microservices, Design Patterns and still learning new technologies.
Please correct the 2nd part.
var JoinMultipleDSUsingMS =
employees.Join(addresses, emp1 => emp1.AddressId, add1 => add1.ID, (emp1, add1) => new { emp1, add1 })
.Join(departments, empaddr => empaddr.emp1.DepartmentId, dept => dept.ID, (empaddr, dept) => new { empaddr, dept })
.Select(e => new
{
ID = e.empaddr.emp1.ID,
EmployeeName = e.empaddr.emp1.Name,
AddressLine = e.empaddr.add1.AddressLine,
DepartmentName = e.dept.Name
}).ToList();
Your are right!
Could you please check your code once more and compare it with the example code? We have reverified the example code and it is working as expected.
The two methods produce different results.
We could not find any differences in the output. Could you please check your code once more and compare with the example code?
Fantastic Tutorial