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.