Understand Self Join in Linq | Self Join with Lambada Expression

In this post, I will explain self join in LINQ and Entity Framework.I’m working on a project where I need to display the employee details in a table with their Manager Name as it is ManagerId in the table as below Image.

For that, I’m deiced to the self Join.I have table Employees with column Emp_Id and Mng_Id.Now I want to perform self join on this.I want to show the employee name and his 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
                         };
        }
    }
}

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 

                         };

        }
    }
}