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

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

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

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

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

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

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.