Understand Self Join in Linq | Self Join with Lambada Expression

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
                         };
        }
    }
}

• 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 

                         };

        }
    }
}

• 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.