用于联接三个表的 Lambda 表达式
本文关键字:三个 Lambda 表达式 用于 | 更新日期: 2023-09-27 18:37:27
>员工表
EmpID
Emp_First_Name
Manager_ID
Department_ID
RoleID
部门表
Department_ID
Department_N
阿梅
角色表
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 表达式显示相同的输出?
你可以这样做作为连续的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
});