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.

Performing Inner Joins in LINQ

To start with I have created a database schema with 2 tables as;

1. LeftTable

    2. RightTable

Added the sample data as;

If inner join is applied on these 2 tables the result set will return rows when there is at least one match in both the tables.

Using SQL syntax for inner join we get following results;

SELECT lefttable.lefttablefield1, lefttable.lefttablefield2, lefttable.lefttablefield3

FROM lefttable

INNER JOIN righttable

ON lefttable.lefttablefield1 = righttable.lefttablereferrence

I applied the join syntax which we are using in Dynamic Budgets in most of the cases and got the same result set;

var qry = from leftTable in instance.LeftTable

join rightTable in instance.RightTable on

new { a = leftTable.LeftTableField1 } equals

new { a = rightTable.LeftTablereferrence }

select leftTable;

Found result as;

Perform LINQ join on nullable and non-nullable types

When working with Nullable types, join statement in LINQ need to be handled carefully. When you perform a join over nullable and non-nullable types,
compile time exception is thrown saying that "Type inference failed in the call to join". For example consider following classes.

public class Employee
{
   public int EmployeeId
   {
      get;
      set;
   }
        
   public string EmployeeName
   {
      get;
      set;
   }
}

public class Employee
{
   public int EmployeeId
   {
      get;
      set;
   }
        
   public string EmployeeName
   {
      get;
      set;
   }
}

To fetch data from these let's write code as;

class Test
   {
       static void Main()
       {
           var employees = new List<Employee>
           {
               new Employee
               {
                   EmployeeId = 1,
                   EmployeeName = "E1",
               },
               new Employee
               {
                   EmployeeId = 2,
                   EmployeeName = "E2",
               },
               new Employee
               {
                   EmployeeId = 3,
                   EmployeeName = "E3",
               },
           };

           var departments = new List<Department>
           {
               new Department
               {
                   EmployeeId = 2,
                   EmployeeName = "E2",
               },
               new Department
               {
                   EmployeeId = null,
                   EmployeeName = "D1",
               },
               new Department
               {
                   EmployeeId = 3,
                   EmployeeName = "E3",
               },
           };

           var r =
               from dept in departments
               where dept.EmployeeId != null
               join emp in employees
               on new { SourceEmployeeID = dept.EmployeeId.Value, SourceEmployeeName = dept.EmployeeName }
               equals new { SourceEmployeeID = emp.EmployeeId, SourceEmployeeName = emp.EmployeeName }
               select new
               {
                   emp,
                   dept,
               };

           foreach (var item in r)
           {
               Console.WriteLine("{0}", item.emp.EmployeeId);
           }
       }
   }
}

The query highlighted in above code will match the LHS with EmployeeID which is nullable with RHS EmployeeID which is not nullable.