Left Outer Join Using Linq

Rakesh reddy

8y892LINQ

List employees = new List();

employees.Add(new Employee { empId = 1, empName = "Emp1", deptId = 1 });

employees.Add(new Employee { empId = 2, empName = "Emp2", deptId = 2 });

employees.Add(new Employee { empId = 3, empName = "Emp3", deptId = 1 });

employees.Add(new Employee { empId = 4, empName = "Emp4", deptId = 3 });

employees.Add(new Employee { empId = 5, empName = "Emp5", deptId = 4 });

List depts = new List();

depts.Add(new Department() { deptId = 1, deptName = "CSE" });

depts.Add(new Department() { deptId = 2, deptName = "IT" });

depts.Add(new Department() { deptId = 3, deptName = "CIVIL" });

I need to Dispaly all the employees with department name, if the departmet is not exists in need to display null for that employye

i tried with

var result = (from emp in employees

join dept in depts on emp.deptId equals dept.deptId

into a

from b in a.DefaultIfEmpty(new Department())

select new

{

empId = emp.empId,

empName = emp.empName,

deptId = b.deptId,

department = b.deptName

});

but still it is retrunign only 4 Employees whoever having the departments in Departmet table.

Can any one help in this.

4 Replies

Know the answer? Post it — somebody with the same question will find it here.

Shubham Kule8y ago
Accepted answer
try this
var result = (from emp in employees
                       join dept in depts on emp.deptId equals dept.deptId
                      into a
                     from b in a.DefaultIfEmpty(new Department())
                     select new
                    {
                       empId = emp.empId,
                       empName = emp.empName,
                       deptId = (int ?) b.deptId,
                       department = b.deptName
                    });
i hope it will help..
Rakesh reddy8y ago
Thank you Subham,

In your query i am gettign result as i am expected and dept id gettigng as 0 instead of 4 for empId= 5 record, to resolve this i tried like below it is solved.
var result = (from emp in employees
join dept in depts on emp.deptId equals dept.deptId
into a
from b in a.DefaultIfEmpty(new Department() { deptId = emp.deptId })
select new
{
empId = emp.empId,
empName = emp.empName,
deptId = (int?)b.deptId,
department = b.deptName
});
Rakesh reddy8y ago
Hi Subham,

Here my requirement is like same, if no department is exists in department table in final result i have to get department name as null like our sql left outer join.
Shubham Kule8y ago
You have only three department and mistakly add id 4. so thats why you returning only 4 Employees