Group Join in Linq

In SQL Server we have nothing like Group Join to get grouped results we perform a left outer join and then group the results based on some column,

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 GroupJoin operator to directly perform group join , Group Join produces hierarchical data structures where each element is paired with the related element in the another collection

Group Join in Method Syntax

Let us understand Group Join with an example. A Department may have ZERO or MORE employees.

      public static void Main()
        {
            OrganizationContext db = new OrganizationContext();
            var employeesByDepartment = db.Departments
                                       .GroupJoin(db.Employees,
                                         d => d.Id,
                                         e => e.DepartmentId,
                                         (department, employees) => new
                                         {
                                           Department = department.Name,
                                           Employees = employees.Count()
                                         });
            foreach (var item in employeesByDepartment)
            {
                Console.WriteLine("{0}    {1}", item.Department, item.Employees);
                Console.WriteLine();
            }
            Console.ReadLine();
        }

Output:.

Group Join in Query Syntax

GroupJoin operator in query syntax works a little differently than method syntax, It requires four expressions as an outer sequence, inner sequence, key selector and result selector. 'on' keyword is used for key selector where the left side of 'equals' operator acts as the outerKeySelector and the right side of 'equals' becomes the innerKeySelector. we Use the into keyword for group join in query syntax.

Example 2: Rewrite the query used inExample 1 using SQL like syntax.

   public static void Main()
        {
            OrganizationContext db = new OrganizationContext();
            var employeesByDepartment = from d in db.Departments
                                        join e in db.Employees
                                        on d.Id equals e.DepartmentId into eGroup
                                        select new
                                         {
                                           Department =d.Name ,
                                           Employees = eGroup.Count()
                                         };
            foreach (var item in employeesByDepartment)
            {
                Console.WriteLine("{0}    {1}", item.Department, item.Employees);
                Console.WriteLine();
            }
            Console.ReadLine();
        }

Please note: So, In query syntax, we need one additional keyword that is the "into" keyword with Join operator. The into keyword is used to group the results of the join, and in this way we achieve the group join.