Undestand-How to Perform Self join in Entity framework

Undestand-How to Perform Self join in Entity framework

In this article, I will explain self join using Entity framework step by step. 
I have table Employees with column Emp_Id and Mng_Id. Now I want to perform self join on this using the Entity Framework.I need to display the employee name and it’s Manager details below Image.
Let start by adding a console application and Add 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
                         };

        }
    }
}

 

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;

        }
    }
}

 

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.