Friday, 7 December 2012

Left and Right Outer Join in LINQ Query

Say we have Staff and Department entities as below:
List<Staff> staffs = new List<Staff>() { 
 new Staff { FullName = "Person 1", DepartmentId =1 },
 new Staff { FullName = "Person 2", DepartmentId =1 },
 new Staff { FullName = "Person 3", DepartmentId =1 },
 new Staff { FullName = "Person 4", DepartmentId =2 },
 new Staff { FullName = "Person 5", DepartmentId =2 },
 new Staff { FullName = "Person 6", DepartmentId =3 },
 new Staff { FullName = "Person 7", DepartmentId =3 }
};

List<Department> departments = new List<Department>() {
 new Department { DepartmentId = 1, Name = "Dept One"},
 new Department { DepartmentId = 3, Name = "Dept Three"},
 new Department { DepartmentId = 4, Name = "Dept Four"},
 new Department { DepartmentId = 5, Name = "Dept Five"}
};

To do Left Outer Join Staff with Department is:
var leftJoinQuery = 
  from staff in staffs
  join dept in departments on staff.DepartmentId equals dept.DepartmentId into joinedStaffDept
  from r in joinedStaffDept.DefaultIfEmpty()
  //select r;  // this returns 'dept' list
  select new { 
    staff.FullName, 
    DeptName = r != null ? r.Name : null
    //DepartmentName = dept != null ?dept.Name : null  // using 'dept' here does not work
  };
Note that the combined result 'joinedStaffDept' actually consists of the first entity only 'Staff'. It doesn't include 'Department'.
Below is the result:


To do Right Outer Join, we need to swap the order of the entities joined:
var rightJoinQuery = 
  from dept in departments
  join staff in staffs on dept.DepartmentId equals staff.DepartmentId into joinedDeptStaff
  from r in joinedDeptStaff.DefaultIfEmpty()
  //select r; // this returns 'staff' list
  select new {
    FullName = r != null? r.FullName : null, // using 'staff' here does not work
    dept.Name
 };
The result: