Rakesh reddy

Rakesh reddy

  • 1.4k
  • 201
  • 5.1k

Left Outer Join Using Linq

Jun 22 2018 3:29 AM
List<Employee> employees = new List<Employee>();
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<Department> depts = new List<Department>();
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. 
 
 

Answers (4)