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

Labels

.net .Net Instrumentation logging .net localization Agile amazon amazon elasticache amazon services AppDomain Application Domain architecture asp ASP.Net authentication authentication mechanisms Byte order mark c# cache cdata certifications class classic mode cloud cloud computing cluster code-behind Combobox compilation Configuration providers configurations connection connectionString constructors control controls contructor CSV CTS .net types conversion database DataGridView DataSource DataTable DataType DBML delegates design pattern dispose double encoding Entity framework Events exception handling expiry fault contracts fault exceptions function pointers functions generics help HostingEnvironmentException IIS inner join instance management integrated mode javascript join left outer join LINQ LINQ join LINQ to SQL memory leak methods microsoft modes in IIS MSIL multiple catch blocks no primary key Nullable Osmos Osmotic Osmotic communication Osmotic communications page events page life cycle partial class PMI preserve precision points private contructor ProcessExit Project management properties property protect connectionString providerName providers query regular expression repository Responsive Web Design return type run-time RWD Saas self join session session expiry sessions singelton singleton pattern software as a service source control system SQLMetal string toolstrip ToolStrip controls ToolStripControlHost tortoise SVN ToString() try catch finally update wcf web application web design web site web.config where-clause xml