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: