SQL Join in LINQ (LINQ to Entity / LINQ to SQL)

Output

Full outer join in LINQ

A full outer join is a logical union of a left outer join and a right outer join. LINQ does not support full outer joins directly, the same as right outer joins.

C# syntax

using (JoinEntities Context = new JoinEntities())  
{  
    var leftOuterJoin = from e in Context.EmployeeMasters  
    join d in Context.DepartmentMasters on e.DepartmentId equals d.DepartmentId into dept  
    from department in dept.DefaultIfEmpty()  
    select new  
    {  
        EmployeeCode = e.Code,  
        EmployeeName = e.Name,  
        DepartmentName = department.Name  
    };  
    var rightOuterJoin = from d in Context.DepartmentMasters  
    join e in Context.EmployeeMasters on d.DepartmentId equals e.DepartmentId into emp  
    from employee in emp.DefaultIfEmpty()  
    select new  
    {  
        EmployeeCode = employee.Code,  
        EmployeeName = employee.Name,  
        DepartmentName = d.Name  
    };  
    leftOuterJoin = leftOuterJoin.Union(rightOuterJoin);  
    Console.WriteLine("Employee Code\tEmployee Name\tDepartment Name");  
    foreach (var data in leftOuterJoin)  
    {  
        if(!string.IsNullOrEmpty(data.EmployeeCode))  
        Console.WriteLine(data.EmployeeCode + "\t\t" + data.EmployeeName + "\t" + data.DepartmentName);  
        else  
        Console.WriteLine(data.EmployeeCode + "\t\t" + data.EmployeeName + "\t\t" + data.DepartmentName);  
    }  
}

Output

Cross Join in LINQ

A cross join is also known as a Cartesian Join. This join does not require any condition in the join but LINQ does not allow using the "join" keyword without any condition. Using two from clauses we can do a cross join.

C# syntax

using (JoinEntities Context = new JoinEntities())  
{  
    var crossJoin = from e in Context.EmployeeMasters  
    from d in Context.DepartmentMasters  
    select new  
    {  
        EmployeeCode = e.Code,  
        EmployeeName = e.Name,  
        DepartmentName = d.Name  
    };  
    Console.WriteLine("Employee Code\tEmployee Name\tDepartment Name");  
    foreach (var data in crossJoin)  
    {  
        Console.WriteLine(data.EmployeeCode + "\t\t" + data.EmployeeName + "\t" + data.DepartmentName);  
    }  
}

Output

Group join in LINQ

Generally, in SQL, a group join can be done using a "Group by" clause. There are two ways to do a group join in LINQ.

1. Using INTO keyword

C# syntax

using (JoinEntities Context = new JoinEntities())  
{  
    var groupJoin = from d in Context.DepartmentMasters  
    join e in Context.EmployeeMasters on d.DepartmentId equals e.DepartmentId into emp  
    select new  
    {  
        DeparmentCode = d.Code,  
        DeparmentName = d.Name,  
        Employee = emp  
    };  
    foreach (var data in groupJoin)  
    {  
        Console.WriteLine("Department:" + data.DeparmentCode + " - " + data.DeparmentName);  
        if (data.Employee != null && data.Employee.Count() > 0)  
        {  
        Console.WriteLine("Employee Code\tEmployee Name");  
        foreach (var empData in data.Employee)  
        {  
            Console.WriteLine(empData.Code + "\t\t" + empData.Name);  
        }  
    }  
    else  
    {  
    Console.WriteLine("Department has no employee.");  
    }  
        Console.WriteLine("");  
        Console.WriteLine("");  
    }  
}

2. Using sub query

using (JoinEntities Context = new JoinEntities())  
{  
    var groupJoin = from d in Context.DepartmentMasters  
    select new  
    {  
        DeparmentCode = d.Code,  
        DeparmentName = d.Name,  
        Employee = (from e in Context.EmployeeMasters  
        where e.DepartmentId == d.DepartmentId  
        select e)  
    };  
    foreach (var data in groupJoin)  
    {  
        Console.WriteLine("Department:" + data.DeparmentCode + " - " + data.DeparmentName);  
        var employees = data.Employee as IEnumerable<EmployeeMaster>;  
        if (employees != null && employees.Count()> 0)  
        {  
            Console.WriteLine("Employee Code\tEmployee Name");  
            foreach (var empData in employees)  
            {  
               Console.WriteLine(empData.Code + "\t\t" + empData.Name);  
            }  
       }  
       else  
       {  
           Console.WriteLine("Department has no employee.");  
       }  
       Console.WriteLine("");  
       Console.WriteLine("");  
    }  
}

Output

Summary

This article may help you to implement a join in LINQ.