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:

No comments:
Post a Comment