Performing self join uisng LINQ

Self join is used when one wants to refer data from the same table. Consider a scenario where we have an Employee table. Each record consist of ManagerID which is again an Employee. Now if we want to get the record of employee-manager relationship.

Consider a class Employee as,

1
2
3
4
5
6
7
public class Employee
    {
        public int EmpID { get; set; }
        public string EmpName { get; set; }
        public string City { get; set; }
        public int ManagerID { get; set; }
    }
 
We will create a list of Employee( I am using class, you can fetch records from database table)

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
List<Employee> emp = new List<Employee>();
emp.Add(new Employee()
{
    EmpID = 1,
    EmpName = "a",
    City = "Pune",
    ManagerID = 11
});

emp.Add(new Employee()
{
    EmpID = 2,
    EmpName = "b",
    City = "mumbai",
    ManagerID = 12
});

emp.Add(new Employee()
{
    EmpID = 3,
    EmpName = "c",
    City = "Pune",
    ManagerID = 2
});

emp.Add(new Employee()
{
    EmpID = 4,
    EmpName = "d",
    City = "Delhi",
    ManagerID = 14
});

         
Now to query data, self join is written as,
1
2
3
var q = (from employee in emp
        join employee2 in emp on employee.EmpID equals employee2.ManagerID
        select employee).FirstOrDefault();
     
Using this query you can select child employee as well as parent employee i.e. employee as well as Manager.

2 comments:

  1. Well done!

    Please insert one record (or many records) whose Manager_ID is null. Managers/Employees reporting to no one (or parent record).

    I will realize that you need to change the query bit..

    ReplyDelete
  2. Query might change based on real requirement. This is just sample :)

    ReplyDelete