How to Perform Self Join in Entity framework

In this article, I will explain how to perform a self-join using Entity Framework step by step.I have a table called Employees with columns Emp_Id and Mng_Id. Now, I want to perform a self-join on this table using Entity Framework. The goal is to display the employee name and its manager details, as shown in the image below.

Let's begin by adding a console application and adding the ADO .NET Entity Data Model.

Sql Table Script:-

CREATE TABLE [dbo].[Employee](
    [Emp_Id] [int] NOT NULL,
    [Name] [nvarchar](50) NULL,
    [Mng_Id] [int] NULL,
    [Contact] [nvarchar](50) NULL,
    [Address] [nvarchar](150) NULL,
 CONSTRAINT [PK_Employee] PRIMARY KEY CLUSTERED 
(
    [Emp_Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

Self Join Using Left Join with Sql Query :-

select e.Name EmployeeName,m.Name Managername  , m.contact MangerContact,m.Address MangerAddress
from Employee e
Left join Employee m on e.Mng_Id=m.Emp_Id

Self Joint Using Left Join with Entity Query :-

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace ConsoleApplication
{
        class Program
    {
        static void Main(string[] args)
        {
            DemoDataBaseEntities db = new DemoDataBaseEntities();
        var result = from e in db.Employees
                         join m in db.Employees on e.Mng_Id equals m.Emp_Id into temp
                         from res in temp.DefaultIfEmpty()
                         select new
                         {
                             Emploayee = e.Name,
                             Manager = res.Name,
                             MangerContact=res.Contact,
                             MangerAddress=res.Address
                         };

        }
    }
}

LINQ Query:

from e in db.Employees: This line specifies the first table Employees and aliases it as e.

join m in db.Employees on e.Mng_Id equals m.Emp_Id into temp: This line performs a join operation on the Employees table with itself based on the condition that the Mng_Id of an employee matches the Emp_Id of another employee. The results are stored in the temp variable.

from res in temp.DefaultIfEmpty(): This line uses a left outer join operation to include all employees, even those without managers. It assigns the results to the res variable.

select new { ... }: This line constructs a new anonymous type for each result, containing properties for the employee's name (Emploayee), the manager's name (Manager), manager's contact (MangerContact), and manager's address (MangerAddress).

This LINQ query retrieves employee details along with their respective manager's details, including contact information and address, if available.

Result:

Self Join Using Inner Join with Sql Query :-


SELECT  c2.* 
FROM    Employee c1 INNER JOIN Employee c2
ON      c1.Emp_Id = c2.Mng_Id

Self Joint Using Inner Join with Entity Query :-

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace ConsoleApplication
{
        class Program
    {
        static void Main(string[] args)
        {
            DemoDataBaseEntities db = new DemoDataBaseEntities();
        var result = from c1 in db.Employees
                        join c2 in db.Employees on c1.Emp_Id equals c2.Mng_Id
                        select c2;

        }
    }
}

LINQ Query:

from c1 in db.Employees: This line specifies the first table Employees and aliases it as c1.

join c2 in db.Employees on c1.Emp_Id equals c2.Mng_Id: This line performs a join operation between the Employees table and itself based on the condition that the Emp_Id of an employee (c1) matches the Mng_Id of another employee (c2).

select c2;: This line selects the resulting employee (c2) from the join operation.

Result:

The result variable holds the query result, which includes employees who are managers (identified by c2) based on the join condition.

Overall, above LINQ query retrieves employees from the Employees table who are also managers, based on the relationship between Emp_Id and Mng_Id.