[转贴]怎样在LINQ实现 LEFT JOIN 或者RIGHT JOIN

In this post let us see how we can handle Left Join and Right Join when using LINQ. There are no keywords defined in C#, we have to use DefaultIfEmpty() function to get the desired result.
Let us see how we can achieve it.

To make you understand better I use a Employee -> Department realation to explain.

First we shall create two classes namely Employee and Department

class Employee    
{
public string Name { get; set; }
public int ID { get; set; }
public int DeptID { get; set; }
}

class Department    
{
public int ID { get; set; }
public string Name { get; set; }
}

 

Lets create some objects of both the classes and fill some dummy data in it.

Employee emp1 = new Employee() { ID = 1, Name = "A", DeptID = 1};
Employee emp2 = new Employee() { ID = 2, Name = "B", DeptID = 1};
Employee emp3 = new Employee() { ID = 3, Name = "C", DeptID = 1 };
Employee emp4 = new Employee() { ID = 4, Name = "D", DeptID = 2 };
Employee emp5 = new Employee() { ID = 5, Name = "E", DeptID = 2 };
Employee emp6 = new Employee() { ID = 6, Name = "F", DeptID = 2 };
Employee emp7 = new Employee() { ID = 7, Name = "G", DeptID = 6 };
Employee emp8 = new Employee() { ID = 8, Name = "H", DeptID = 3 };
Employee emp9 = new Employee() { ID = 9, Name = "I", DeptID = 3 };
Employee emp10 = new Employee() { ID = 10, Name = "J", DeptID = 7};
Employee emp11 = new Employee() { ID = 11, Name = "K", DeptID = 7};
Employee emp12 = new Employee() { ID = 12, Name = "L", DeptID = 5};

Department Dept1 = new Department() { ID = 1, Name = "Development"};
Department Dept2 = new Department() { ID = 2, Name = "Testing"};
Department Dept3 = new Department() { ID = 3, Name = "Marketing"};
Department Dept4 = new Department() { ID = 4, Name = "Support"};

List<Employee> ListOfEmployees = new List<Employee>();
ListOfEmployees.AddRange((new Employee[] { emp1, emp2, emp3, emp4, emp5, emp6, emp7,
emp8, emp9, emp10, emp11, emp12 }));

List<Department> ListOfDepartment = new List<Department>();
ListOfDepartment.AddRange( new Department[]{ Dept1,Dept2,Dept3,Dept4});

 

So we finish loading the objects into ListOfEmployees and ListOfDepartments, using this lists we shall see how we can join them to get the results.
First let us see what would be the query in SQL if we had the same structure in our tables.
For Left join and right join we would have used the query

--Left Join in SQL
select Emp.Name, Dept.Name from Employee Emp left join Department Dept on
Emp.DeptID = Dept.ID

--Right Join In SQL
select Emp.Name, Dept.Name from Employee Emp right join Department Dept on
Emp.DeptID = Dept.ID

 

Using LINQ, Left Join can be acheived as follows

var LeftJoin = 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,
DepartmentName = dept != null ? dept.Name : null                          
};

 

And for Right Join there is no pretty difference, we just need to reverse the joining in first 2 lines. Here it follows

var RightJoin = from dept in ListOfDepartment
join employee in ListOfEmployees
on dept.ID equals employee.DeptID into joinDeptEmp
from employee in joinDeptEmp.DefaultIfEmpty()
select new                            
{
EmployeeName = employee != null ? employee.Name : null,
DepartmentName = dept.Name
};