Back to: LINQ Tutorial For Beginners and Professionals
LINQ Join with Multiple Data Sources in C#
In this article, I am going to discuss How to Perform LINQ Inner Join with Multiple Data Sources in C# with Examples. I strongly recommended you read our previous article before proceeding to this article where we discussed how to perform LINQ Inner Join with two Data Sources in C# with Examples using both Method and Query Syntax.
Examples to Understand LINQ Join with Multiple Data Sources in C#:
We are going to 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 common properties, we are going to perform the Join. The point that you need to remember is, in order to perform the Join Operation, we need to have common property.
Now, 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 Employee Data Source, we need Department property from the Department Data Source and we need 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 which exist in both data sources.
Creating the Model Classes and Data Sources:
So, create a class file with the name Employee.cs and then copy and paste the following code into it. This is a very simple class having 4 properties i.e. Id, Name, and AddressId and DepartmentId. We have also created one method which is going 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 }
};
}
}
} Now, create another class file with the name Address.cs and then copy and paste the following code into it. This is a very simple class having 2 properties i.e. Id, and AddressLine. We have also created one method which is going to return a collection of addresses which is going to be the second data source going to be used in 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 is a very simple class having 2 properties i.e. Id, and Name. We have also created one method which is going to return a collection of departments which is going to 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 Annonymous 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 the name EmployeeResult.cs with the required properties that you want in the result set. As per our requirement, we have created the class with the following four properties.
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, it is a little difficult to write the join query when joining more than two data sources. 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 below example, we are using 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.
In the above example, we have projected the result to an anonymous type. Instead of projecting the Result to an Anonymous Type, we can also project the result to a named type using the LINQ Method Syntax. 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();
}
}
} Now, run the application and you will get the same output as the previous example.
My Personal Opinion: If you want to fetch the data from two data sources then you can use either the Method syntax or Query Syntax. But if you want to fetch the data from more than two data sources then it is easier to use the Query syntax rather than the Method syntax. But there are no performance differences whether you use the Method Syntax or Query Syntax.
In the next article, I am going to 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 understood how to join multiple data sources using LINQ in C# with Examples.
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?