Posted on 2/12/2021 2:07:39 PM by Admin

Left Outer Join in Linq

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.

Left outer Join in Sql Server
left Outer Join in SQL Server

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.

How to think
How to think when working with LINQ

The entity Framework will generate following domain model on the basis of Employee and Department table mentioned above.

Entity Model generated by EF
Entity Model generated by EF

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(z => z.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.

Output
Output

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();
        }

Output
Output