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

Introduction

SQL joins are used to get data from two or more tables, based on the logical relationships between the tables. There are mainly the following four types of joins available with SQL Server:

1. Inner join
2. Outer Join
   • Left Outer join
   • Right Outer join
   • Full outer join
3. Cross Join
4. Group Join

LINQ is a full-featured query language. LINQ also offers JOIN operations. In this article, I will explain how to do these joins in LINQ.

Example

I have two tables: EmployeeMaster and DepartmentMaster. The relation between these two tables and dummy data for the tables are as shown below.

Department table

Employee table

Inner Join in LINQ

An inner join returns only those records that exist in the tables. Using the "join" keyword we can do an inner join using a LINQ query.

C# syntax

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

 

Output

Left outer join in LINQ

A Left Outer join returns all records from the left table and the matching record from the right table. If there are no matching records in the right table then it returns null. If we want to do a Left Outer join in LINQ then we must use the keyword "into" and method "DefaultIfEmpty".

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  
    };  
    Console.WriteLine("Employee Code\tEmployee Name\tDepartment Name");  
    foreach (var data in leftOuterJoin)  
    {  
        Console.WriteLine(data.EmployeeCode + "\t\t" + data.EmployeeName + "\t" + data.DepartmentName);  
    }  
}

Output

Right outer join in LINQ

A right outer join is not possible with LINQ. LINQ only supports left outer joins. If we swap the tables and do a left outer join then we can get the behavior of a right outer join.

C# syntax

using (JoinEntities Context = new JoinEntities())  
{  
    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  
    };  
    Console.WriteLine("Employee Code\tEmployee Name\tDepartment Name");  
    foreach (var data in rightOuterJoin)  
    {  
        Console.WriteLine(data.EmployeeCode + "\t\t" + data.EmployeeName + "\t" + data.DepartmentName);  
    }  
}

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.