c# - Linq 与 Lambda 等效于 SQL

Question

我是 Entity Framework 的新手，我一直在尝试使用包含简单连接的 Lambda 编写 Linq 语句。我有三个表:Staff - StaffRole - Role。

我想要满足特定条件的特定角色的工作人员。用常规 SQL 编写它非常简单:

SELECT *
FROM Staff s
INNER JOIN StaffRole sr ON s.StaffId = sr.StaffId
INNER JOIN Role r ON sr.RoleId = r.RoleId
WHERE r.Description = 'SpecialisedHealthManager'
AND s.PrimaryShm = 0

现在，在 Linq 语句中编写它并没有给我太多运气。我认为它会是这样的:

var actingShm = db.Staff.Join(db.StaffRole,
                           inner => inner.StaffId,
                           outer => outer.Role,
                           (outer, inner) => new
                           {
                               StaffId = inner.StaffId,
                               FirstName = inner.Staff.FirstName,
                               Surname = inner.Staff.Surname,
                               SamAccountName = inner.Staff.SamAccountName,
                               RoleId = outer.Description
                           });

不用说，这是行不通的..

Answer 1

尝试这样使用它:

var list = from s in Staff
join sr in StaffRole on s.StaffId equals sr.StaffId
join r in Role on sr.RoleId equals r.RoleId
where r.Description == 'SpecialisedHealthManager' && s.PrimaryShm == 0
select new
{
   StaffId = s.StaffId,
   FirstName = s.Staff.FirstName,
   Surname = s.Staff.Surname, 
   SamAccountName = s.Staff.SamAccountName,
   RoleId = r.Description
});