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.