Understand Self Join in Linq | Self Join with Lambada Expression

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



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:-

 

Ashok Patel

I'm an electronic engineer working in a multinational company,having good experience on Electronics and electrical engineers design and oversee production of electronic equipment such as radios, televisions, computers, washing machines and telecommunication systems.I like to do RND and Research.I also have hands on experience graphic design software and in web designing having great command on ASP.NET, HTML5, JavaScript, T-SQL, JQuery.

Add comment

Donate for Corona Victim

Corona Virus Relief Fund

Your Header Sidebar area is currently empty. Hurry up and add some widgets.