C# - LINQ Join Operators (Inner Join Left Join, Group Join, Cross Join)

In LINQ, joining operators are used to join two or more lists / collections and get matched data from the collections based on specified expressions. The LINQ join operators behavior and functionality will be same as SQL joins. 

Operator	Description
Inner Join	It return elements from the collections which satisfies specified expressions
Left Outer Join	It return all the elements from left side collection and matching elements from the right side collection
Cross Join	It returns cartesian product of elements in collections
Group Join	A join clause with an into an expression is called a group join

In LINQ, Inner join is used to return only the matched records or elements from the collections based on specified conditions.

var result = from d in objDept

             join e in objEmp

                on d.DepId equals e.DeptId

                    select new

                    {

                       EmployeeName = e.Name,

                       DepartmentName = d.DepName

                    };

In LINQ, LEFT JOIN or LEFT OUTER JOIN is used to return all the records or elements from left side collection and matching elements from the right side collection. 

In LINQ to achieve LEFT JOIN behavior, it's mandatory to use "INTO" keyword and "DefaultIfEmpty()" method.

var result = from e in objEmp

  join d in objDept

on e.DeptId equals d.DepId 

into empDept

  from ed in empDept.DefaultIfEmpty()

select new

{

EmployeeName = e.Name,

DepartmentName = ed == null ? "No

Department" : ed.DepName

}

In LINQ, Cross join will produce Cartesian product of collection items and we don't need any condition to join collections. In LINQ Cross join each element in left side collection will be mapped to all the elements in right side collection.

var result = from e in objEmp

  from d in objDept

select new

                 {

                     EmployeeName = e.Name,

                     DepartmentName = d.DepName

                 };

In LINQ, a Join clause with an into expression is called a Group join. In LINQ, Group join produces a sequence of object elements based on the matching elements from both left and right collections. In case, If no matching elements found between the right and left collections, then the join clause will return an empty array. In LINQ, group join is an equivalent to INNER EQUI JOIN except that the result elements organized into groups. In C# or vb.net applications, we can use the result of LINQ GROUP JOIN clause as a subquery based on our requirements.

var result = from d in objDept

             join e in objEmp on d.DepId equals e.DeptId 

                 into empDept

select new

{

DepartmentName = d.DepName,

Employees = from emp2 in empDept

 orderby emp2.Name

  select emp2

};