How to perform Linq to Entites Left Outer Join

Question

I have read plenty of blog posts and have yet to find a clear and simple example of how to perform a LEFT OUTER JOIN between two tables. The Wikipedia article on joins Join (SQL) provides this simple model:

CREATE TABLE `employee` (
`LastName` varchar(25),
`DepartmentID` int(4),
UNIQUE KEY `LastName` (`LastName`)
);

CREATE TABLE `department` (
`DepartmentID` int(4),
`DepartmentName` varchar(25),
UNIQUE KEY `DepartmentID` (`DepartmentID`)
);

Assume we had a EmployeeSet as an employee container ObjectSet<Employee> EmployeeSet and a DepartmentSet ObjectSet<Department> DepartmentSet. How would you perform the following query using Linq?

SELECT LastName, DepartmentName
FROM employee e 
  LEFT JOIN department d 
    ON e.DepartmentID = d.DepartmentID

Answer 1

You need to use the DefaultIfEmpty method :

var query =
    from e in db.EmployeeSet
    join d in db.DepartmentSet on e.DepartmentID equals d.DepartmentID into temp
    from d in temp.DefaultIfEmpty()
    select new { Employee = e, Department = d };

Answer 2

I would write this, which is far simpler than join and does exactly the same thing:

var q = from e in db.EmployeeSet
        select new 
        {
            LastName = e.LastName,
            DepartmentName = e.Department.DepartmentName
        };