Outer joins using LINQ
In this article I will demonstrate how to write outer joins in LINQ.
By default; there is no keyword for left or right join in LINQ but there is a way to achieve the same by using DefaultIfEmpty method.
The following samples will show you how to write left outer join. It uses classic employee and department relation.
1. Have two classes - Employee and Department
class Employee
{
public int ID { get; set; }
public string Name { get; set; }
public int DeptID { get; set; }
}
class Dept
{
public int DeptID { get; set; }
public string DeptName { get; set; }
}
2. Fill it with values
List
new Employee{ ID=1, Name="Test1", DeptID =2},
new Employee{ ID=2, Name="Test2", DeptID =7}
};
List
new Dept{ DeptName="Dep1", DeptID =4},
new Dept{ DeptName="Dep2", DeptID =2}
};
(/CODE]
Note: I have assigned an employee ("Test2") with a dept which never exists.
3. Write the LINQ
var result = from emp in employeeList
join dept in deptList
on emp.DeptID equals dept.DeptID into resultSet
from dept in resultSet.DefaultIfEmpty()
select new
{
employee = emp.Name,
department = dept != null ? dept.DeptName : null
};
The result will be
Test1 2
Test2 null
Similar methodology can be used for right outer join as well.
Best Regards,
Sudeep Syamnath
I just do not understand how dept.DeptName is included if the resultant type is a result of LeftOuterJoin.