Blog

In SQL, we have Inner, left outer, right outer, and full outer joins. For simulating them in Entity Framework in Linq componet we should do some innovation! Let’s consider with example.
 

SELECT e.Name AS EmployeeName, d.Name AS DepartmentName

FROM Employees e

INNER JOIN Departments d ON e.DepartmentId = d.Id;

To create the LINQ version of the above inner join:
 

var query = from employee in dbContext.Employees

            join department in dbContext.Departments

            on employee.DepartmentId equals department.Id

            select new { EmployeeName = employee.Name, DepartmentName = department.Name };

For the following left outer join:
 

SELECT d.Name AS DepartmentName, e.Name AS EmployeeName

FROM Departments d

LEFT OUTER JOIN Employees e ON d.Id = e.DepartmentId;

 

we can write this:

 

var query = from department in dbContext.Departments

            join employee in dbContext.Employees

            on department.Id equals employee.DepartmentId into employeesInDepartment

            from emp in employeesInDepartment.DefaultIfEmpty()

            select new { DepartmentName = department.Name, EmployeeName = emp != null ? emp.Name : null };

For the following right outer join:
 

SELECT e.Name AS EmployeeName, d.Name AS DepartmentName

FROM Employees e

RIGHT OUTER JOIN Departments d ON e.DepartmentId = d.Id;

We can achieve this via this:
 

var query = from employee in dbContext.Employees

            join department in dbContext.Departments

            on employee.DepartmentId equals department.Id into departmentEmployees

            from deptEmp in departmentEmployees.DefaultIfEmpty()

            select new { EmployeeName = employee.Name, DepartmentName = deptEmp != null ? deptEmp.Name : null };

 

Finally for the following full outer join:
 

SELECT d.Name AS DepartmentName, e.Name AS EmployeeName

FROM Departments d

LEFT OUTER JOIN Employees e ON d.Id = e.DepartmentId



UNION



SELECT e.Name AS EmployeeName, d.Name AS DepartmentName

FROM Employees e

RIGHT OUTER JOIN Departments d ON e.DepartmentId = d.Id;

The following trick could cover it:
 

var leftOuterJoin = from department in dbContext.Departments

                    join employee in dbContext.Employees

                    on department.Id equals employee.DepartmentId into departmentEmployees

                    from deptEmp in departmentEmployees.DefaultIfEmpty()

                    select new { DepartmentName = department.Name, EmployeeName = deptEmp != null ? deptEmp.Name : null };



var rightOuterJoin = from employee in dbContext.Employees

                     join department in dbContext.Departments

                     on employee.DepartmentId equals department.Id into departmentEmployees

                     from empDept in departmentEmployees.DefaultIfEmpty()

                     select new { EmployeeName = employee.Name, DepartmentName = empDept != null ? empDept.Name : null };



var fullOuterJoin = leftOuterJoin.Union(rightOuterJoin);