In SQL Server when we perform a Left join, then all the records from left Table and only the matched records from Right Table are included in the result set and Non - Matching records from Right Table are simply excluded from the result set as shown in the below diagram.
As you can see The BA department has no employees yet it is included in the result set
But with Linq we don't query a database tables directly , rather we query the Collection of Objects .The Entity Framework converts the tables into collection of objects , and we simply query those collections (DbSets) and then the Linq provider translates our query to corresponding T-SQL.
The entity Framework will generate following domain model on the basis of Employee and Department table mentioned above.
In Linq we have no specific Join operator to perform Left outer join ,we Use DefaultIfEmpty() method on the results of a group join to implement Left Outer Join, If we have 2 collections, and we perform a Group Join and then apply DefaultIfEmpty() method on the results, then all elements from first collection and matching elements from the second collections are included in the result set , Non - Matching elements are excluded.
Left Join in Method Syntax
public static void Main()
{
OrganizationContext db = new OrganizationContext();
var result = db.Departments
.GroupJoin(db.Employees,
d => d.Id,
e => e.DepartmentId,
(dep, emps) => new { dep, emps })
.SelectMany(g=> g.emps.DefaultIfEmpty(),
(a, b) => new
{
Department = a.dep.Name,
EmployeeName = b == null ? "No Employee" : b.Name
});
foreach (var item in result)
{
Console.WriteLine("{0} {1}", item.Department, item.EmployeeName);
Console.WriteLine();
}
Console.ReadLine();
}
The point is, to implement Left Outer Join, with extension method syntax we use the GroupJoin() method along with SelectMany() and DefaultIfEmpty() methods.
Output: Notice that, we also have BA department record in spite of it has no employees. So this is effectively a left outer join.
Left Outer Join in Query Syntax
We Use DefaultIfEmpty() method on the results of a group join to implement Left Outer Join
Example 2 : Rewrite Example 1 using SQL like syntax.
public static void Main()
{
OrganizationContext db = new OrganizationContext();
var result = from d in db.Departments
join e in db.Employees
on d.Id equals e.DepartmentId into eGroup
from e in eGroup.DefaultIfEmpty()
select new
{
Department = d.Name,
EmployeeName = e == null ? "No Employee" : e.Name
};
foreach (var item in result)
{
Console.WriteLine("{0} {1}", item.Department, item.EmployeeName);
Console.WriteLine();
}
Console.ReadLine();
}