Lambda表达式为连接三个表(Lambda Expression for joining thre

员工表

EmpID
Emp_First_Name
Manager_ID
Department_ID
RoleID

系表

Department_ID
Department_N

AME

角色表

RoleID
RoleName

加入三个表

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
                       };

此查询工作正常。 但我想写Lambda表达式相同的查询。 如何显示使用Lambda表达式相同的输出？

Answer 1:

你可以这样做，因为一连串的Joins ，每个继续与新的前投影加盟，但在评论中提到的，这很快变得混乱和难以遵循：（尤其是如果你习惯于使用SQL联接语法）

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
});

（我保留你的溯源原来的别名，并使用你的模式，如增加了新的别名中间匿名预测edmr是EmployeeDepartmentManagerRole ）

但是，我反而建议您确保这似乎外键关系是存在于表执行，然后通过你的LINQ2SQL DBML模型通航关系扯这些。 启用或者延迟加载，或者用适当的预先加载LoadsWith DataContext的选项设置，您将能够减少查询和投影到简单：

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
});

文章来源: Lambda Expression for joining three tables