Multiple List Left Join in LINQ

We had seen in earlier posts how to left join and right join 2 lists using LINQ. But what about multiple list joining is it in the same way or are there any challanges in the implementation.

Today we shall see how we can use left join on multiple list in a single query.

We will take a case of 3 classes Branch ( ID, Name) , Department(ID , Name, BranchID) and employees(ID,Name,DeptID)

Download Project – 2.9 Kb

If we would join the 3 similar tables in MSSQL then it would appear as

Select Emp.Name, Dept.Name, branch.Name from ((Employees as Emp left join 
Departments as Dept on Dept.ID = Emp.DeptID)left join 
Branches as branch on Dept.BranchID = branch.ID)

In the above query we can see that first we join Employees and department on Department ID and the output of that join is joined with branch on brachid. By following the same flow we can derive the same result using LINQ. The equivalent implementation in LINQ follows

var MultipleListJoin = from empdept in
                                   (from emp in ListOfEmployees
join dept in ListOfDepartment
on emp.DeptID equals dept.ID into JoinedEmpDept
from dept in JoinedEmpDept.DefaultIfEmpty()
select new                                    
{
EmployeeName = emp.Name,
BranchID = dept != null ? dept.BranchID : 0,
DepartmentName = dept != null ? dept.Name : null                                    
})
join branch in ListOfBranch on
                               empdept.BranchID equals branch.ID into JoinedwithBranch
from branch in JoinedwithBranch.DefaultIfEmpty()
select new                               
{
EmpName = empdept.EmployeeName,
DeptName = empdept.DepartmentName,
BranchName = branch != null ? branch.Name : string.Empty
};

In the above first we derive the intermediate ouput by joining Employee and Department

from emp in ListOfEmployees
join dept in ListOfDepartment
on emp.DeptID equals dept.ID into JoinedEmpDept
from dept in JoinedEmpDept.DefaultIfEmpty()
select new                                    
{
EmployeeName = emp.Name,
BranchID = dept != null ? dept.BranchID : 0,
DepartmentName = dept != null ? dept.Name : null}

and then use the intemediate output and join it with Branches to get the final result.

Hope you have got how to join multiple lists, for Right join follow the above implementataion in the reverse way.

Happy Learning

About these ads