Join clause in LINQ is used when we have multiple data sources and we want to match the elements in those data sources based on some common property.For example we can have two Lists and we want to associate every element in the first list with the elements in the second list.
In the following example we have two classes Employee and Department.Employee class represents an employee.Every employee has an Id,Name,DepartmentId and Designation.
Department class defines the Name and Id.If we want to get the department name to which an employee belongs to then we need to match the Employee class and the Department class.We can match these two classes on the common DepartmentId property which is declared in the Department class as Id property.
class Employee
{
public int Id { get; set; }
public int DepartmentId { get; set; }
public string Name { get; set; }
public string Designation { get; set; }
}
class Department
{
public string Name { get; set; }
public int Id { get; set; }
}
We create two lists which contains the objects of Employee and Department types.
List<Employee> lstEmployees = new List<Employee>();
lstEmployees.Add(new Employee {Name = "Ajay", DepartmentId = 1, Id = 1});
lstEmployees.Add(new Employee { Name = "Amit", DepartmentId = 1, Id = 2 });
lstEmployees.Add(new Employee { Name = "John", DepartmentId = 2, Id = 3 });
lstEmployees.Add(new Employee { Name = "Mark", DepartmentId = 2, Id = 4 });
List<Department> lstDepartments = new List<Department>();
lstDepartments.Add(new Department { Name = "Sales", Id = 1});
lstDepartments.Add(new Department { Name = "Marketing", Id = 2});
As every employee in the lstEmployees list has a departmentId so we can use inner join to retrieve the department name to which an employee belongs to.
var empDept = from employee in lstEmployees
join department in lstDepartments
on employee.DepartmentId equals department.Id
select new
{
EmployeeName = employee.Name,
Department = department.Name
};
If we iterate over the empDept list using the following loop
foreach (var empDept in results)
{
Console.WriteLine("Employee Name={0},Department={1}", empDept.EmployeeName,empDept.Department);
}
then we get the following output
Employee Name=Ajay,Department=Sales Employee Name=Amit,Department=Sales Employee Name=John,Department=Marketing Employee Name=Mark,Department=Marketing
Lets say there is an employee Rachel who has not been assigned to any department yet.
lstEmployees.Add(new Employee { Name = "Rachel", DepartmentId = 0, Id = 4 });
So if we use inner join then since Rachel has not been assigned to any department will not be included in the join.To include such elements which are present in only one table we use left outer join
var results =
from employee in lstEmployees
join department in lstDepartments on employee.DepartmentId equals department.Id into prodGroup
from j in prodGroup.DefaultIfEmpty(new Department() { Name = "Unassigned" })
select new
{
DeptName = j.Id,
EmpName = employee.Name,
EmpDesignation=employee.Designation
};
This will give us the following output:
Employee Name=Ajay,Department=1,Designation=Manager Employee Name=Amit,Department=1,Designation=Team Lead Employee Name=John,Department=2,Designation=Customer Care Executive Employee Name=Mark,Department=2,Designation=Manager Employee Name=Rachel,Department=0,Designation=
Even though Rachel is not assigned to any Department ,she is included in the result because of left outer join.
Leave a Reply