CODEWITHSUNDEEP
c#linqlinq-to-sql
Pearl
Pearl

Reputation: 9415

Lambda Expression for joining three tables

Employee Table

EmpID
Emp_First_Name
Manager_ID
Department_ID
RoleID

Department Table

Department_ID
Department_N

ame

Role Table

RoleID
RoleName

Joining Three Tables

var join = from u in db.TBL_Employees
                       join v in db.TBL_Departments
                       on u.Department_ID equals v.Department_ID
                       join x in db.TBL_Employees
                       on u.Manager_ID equals x.Emp_ID
                       join z in db.TBL_Roles
                       on u.RoleID equals z.RoleID
                       select new
                       {
                           Name = u.Emp_First_Name,
                           Department = v.Department_Name,
                           Manager = x.Emp_First_Name,
                           Role = z.RoleName
                       };

This Query is working fine. But I want to write the same query in Lambda Expression. How to display the same output using Lambda Expression?

Upvotes: 1

Views: 3820

Answers (1)

StuartLC
StuartLC

Reputation: 107237

You could do this as a succession of Joins, each continuing the preceding projection with the new join, but as mentioned in the comments, this soon becomes messy and difficult to follow: (especially if you are accustomed to using the Sql join syntax)

var result = db.TBL_Employees
   .Join(db.TBL_Departments, u => u.Department_ID, v => v.Department_ID, 
         (u, v) => new {Employee = u, Department = v})
   .Join(db.TBL_Employees, ed => ed.Employee.Manager_ID, x => x.Emp_ID, 
         (ed, x) => new {EmployeeDepartment = ed, Manager = x})
   .Join(db.TBL_Roles, edm => edm.EmployeeDepartment.Employee.RoleID, z => z.RoleID, 
         (edm, z) => new {EmployeeDepartmentManager = edm, Role = z})
.Select(edmr => new
{
  Name = edmr.EmployeeDepartmentManager.EmployeeDepartment.Employee.Emp_First_Name,
  Department = edmr.EmployeeDepartmentManager.EmployeeDepartment.Department.Department_Name,
  Manager = edmr.EmployeeDepartmentManager.Manager.Emp_First_Name,
  Role = edmr.Role.RoleName
});

(I've retained your original aliases for tracebility, and added new aliases for the intermediate anonymous projections using your pattern, e.g. edmr is EmployeeDepartmentManagerRole)

However, I would instead recommend that you ensure that the foreign key relationships which seem to be present on the tables are enforced, and then pull these through as navigable relationships in your Linq2Sql DBML model. With either lazy loading enabled, or with appropriate eager loading LoadsWith DataContext options set, you will be able to reduce the query and projection into simply:

var result = db.TBL_Employees
   .Select(e => new
{      
    Name = e.Emp_First_Name,
    Department = e.Department.Department_Name,
    Manager = e.Manager.Emp_First_Name,
    Role = e.Role.RoleName
});

Upvotes: 1

Related Questions