In this post, I will explain how to perform a self-join in LINQ and Entity Framework. I'm currently working on a project where I need to display employee details in a table, including their manager's name. The table structure consists of an `Employees` table with columns `Emp_Id` and `Mng_Id`, where `Mng_Id` represents the manager's ID.

To achieve this, I've decided to use a self-join on the `Employees` table. The goal is to retrieve the employee's name along with their manager's name.

Sql Table

CREATE TABLE [dbo].[Employee](
    [Emp_Id] [int] NOT NULL,
    [Name] [nvarchar](50) NULL,
    [Mng_Id] [int] NULL,
 CONSTRAINT [PK_Employee] PRIMARY KEY CLUSTERED 
(
    [Emp_Id] ASC
)
) ON [PRIMARY]

Sql query:-

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

 

Linq 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
                         };
        }
    }
}
Query Reseult:-


  • join m in db.Employees on e.Mng_Id equals m.Emp_Id into temp: This part of the query performs a join operation between the Employees table and itself based on the condition that the Manager ID (Mng_Id) of an employee (e) matches the Employee ID (Emp_Id) of another employee (m). The into temp part groups the results of the join operation.
  • from res in temp.DefaultIfEmpty(): This line handles the left join operation. It specifies that for each record in the Employees table, if there is no matching record in the self-joined Employees table (temp), it will still include the record with a null value for the manager (res).
  • select new { Employee = e.Name, Manager = res.Name }: This part of the query selects specific fields from the joined result set. It creates a new anonymous object with two properties:
  • Employee: This is assigned the name of the employee (e).
  • Manager: This is assigned the name of the manager (res). If the employee does not have a manager (due to the left join), the manager field will be null.

This LINQ query joins the Employees table with itself to retrieve information about employees and their respective managers, returning the results as a collection of anonymous objects with employee names and manager names.


Now i want to show “supermanager” if Mng_Id is null that means Employee doesn’t have any Mng_Id 

Sql Query:-

select e.Name EmployeeName,Case  when m.Name is NULL then 'SuperMager' else m.Name end as ManagerName from Employee e
Left join Employee m on e.Mng_Id=m.Emp_Id 

Linq 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 == null ? "SuperManager" : res.Name 

                         };

        }
    }
}
Query Result:-

  • join m in db.Employees on e.Mng_Id equals m.Emp_Id into temp: This part of the query performs a join operation between the Employees table and itself based on the condition that the Manager ID (Mng_Id) of an employee (e) matches the Employee ID (Emp_Id) of another employee (m). The into temp part groups the results of the join operation.
  • from res in temp.DefaultIfEmpty(): This line handles the left join operation. It specifies that for each record in the Employees table, if there is no matching record in the self-joined Employees table (temp), it will still include the record with a null value for the manager (res).
  • select new { Employee = e.Name, Manager = res.Name == null ? "SuperManager" : res.Name }: This part of the query selects specific fields from the joined result set. It creates a new anonymous object with two properties:
  • Employee: This is assigned the name of the employee (e).
  • Manager: This is assigned the name of the manager (res). If the employee does not have a manager (due to the left join), the manager field will be null. In such cases, the ternary operator is used to check if the manager is null. If it is, the string "SuperManager" is assigned; otherwise, the actual manager name is assigned.

This LINQ query joins the Employees table with itself to retrieve information about employees and their respective managers. If an employee does not have a manager, the string "SuperManager" is assigned as the manager's name.