Linq - Join multiple tables and get output into single result set

Question

I have a Employee table which has DepartmentId column which is a foreignkey to Department table, the Department table has DepartmentId column and DepartmentName column..

In the search functionality the End user gives DepartmentName and i need display all the Employee details who are part of that department.

how can i join the Department table to the Employee table

 List<Employee> employees = new List<Employee>();

 employees = EMPDB.Employees.AsNoTracking().select( e => new Employee()
 {
 FirstName = e.FirstName,
 LastName = e.LastName,
 DepartmentName =

 })where(e.Departmentid == depName).ToList();

I would like to have my employees list like the above

You can put a navigation property Department on the Employee entity and entity framework will do the necessary joining for you. — Apurv Gupta, Apr 15 '19 at 09:21
You should research first before asking. You can refer to https://stackoverflow.com/questions/43624232/getting-employees-in-each-dept-using-linq-lambda — Vikas Gupta, Apr 15 '19 at 09:24
Possible duplicate of [Getting employees in each dept using LINQ LAMBDA](https://stackoverflow.com/questions/43624232/getting-employees-in-each-dept-using-linq-lambda) — Vikas Gupta, Apr 15 '19 at 09:25

score 0 · Answer 1 · answered Apr 15 '19 at 09:24

You can perform a LEFT JOIN with Departments table like

var data = (from emp in EMPDB.Employees
                join dept in EMPDB.Departments on emp.DepartmentId equals dept.Id 
                && dept.DepartmentName == depName into joindata
                from joindata in joindata.DefaultIfEmpty()
                select new 
                { 
                 FirstName = emp.FirstName, 
                 LastName = emp.Lastname,
                 DepartmentName = !string.IsNullOrEmpty(dept.DepartmentName) ? dept.DepartmentName : "No Department" 
                };).ToList();

Linq - Join multiple tables and get output into single result set

1 Answers1