Outer joins using LINQ


In this article I will demonstrate how to write outer joins in LINQ.

By default; there is no keyword for left or right join in LINQ but there is a way to achieve the same by using DefaultIfEmpty method.

The following samples will show you how to write left outer join. It uses classic employee and department relation.

1. Have two classes - Employee and Department


class Employee
{
public int ID { get; set; }
public string Name { get; set; }
public int DeptID { get; set; }
}
class Dept
{
public int DeptID { get; set; }
public string DeptName { get; set; }
}

2. Fill it with values

List employeeList = new List{
new Employee{ ID=1, Name="Test1", DeptID =2},
new Employee{ ID=2, Name="Test2", DeptID =7}
};

List deptList = new List{
new Dept{ DeptName="Dep1", DeptID =4},
new Dept{ DeptName="Dep2", DeptID =2}
};
(/CODE]
Note: I have assigned an employee ("Test2") with a dept which never exists.

3. Write the LINQ

var result = from emp in employeeList
join dept in deptList
on emp.DeptID equals dept.DeptID into resultSet
from dept in resultSet.DefaultIfEmpty()
select new
{
employee = emp.Name,
department = dept != null ? dept.DeptName : null
};

The result will be
Test1 2
Test2 null

Similar methodology can be used for right outer join as well.

Best Regards,
Sudeep Syamnath


Comments

Author: Subhashini Janakiraman31 Dec 2010 Member Level: Silver   Points : 1

I just do not understand how dept.DeptName is included if the resultant type is a result of LeftOuterJoin.

Author: Sudeep Syamnath17 Jan 2011 Member Level: Gold   Points : 1

Hello Ms.Subhashini,

The left table is the Employee table. Right table is the department table.

All matched records from left table are fetched including its matching records from right table.
If there are no matching records in the right table then Null is populated.

DeptName is a column in right table and to demonstrate left join; the same is used in the above example.

Hope it clears the air.

Best Regards,
Sudeep Syamnath



  • Do not include your name, "with regards" etc in the comment. Write detailed comment, relevant to the topic.
  • No HTML formatting and links to other web sites are allowed.
  • This is a strictly moderated site. Absolutely no spam allowed.
  • Name:
    Email: