需要帮助将 SQL 转换为 LINQ

Question

SELECT ra.ResidentID, ra.RoomID, r.Number, ra.StartDate, p.FacilityID
FROM( 
SELECT ResidentID, MAX(StartDate) AS max_start
FROM RoomAssignments
GROUP BY ResidentID
) m
INNER JOIN RoomAssignments ra
ON ra.ResidentID = m.ResidentID
AND ra.StartDate = m.max_start
INNER JOIN Rooms r
ON r.ID = ra.RoomID
INNER JOIN Person p 
ON p.ID = ra.ResidentID
inner join ComplianceStage cs 
ON cs.Id = p.ComplianceStageID
ORDER BY ra.EndDate DESC

我正在尝试弄清楚如何使用 LINQ 将其转换为 C#。我是 C# 和 LINQ 的新手，无法正确触发我的子查询。你们巫师有机会为我打开灯吗？

更新-----------------

我想我已经掌握了它的要点，但是在查询最大开始日期时遇到问题：

var maxQuery =
from mra in RoomAssignments
group mra by mra.ResidentID
select new { mra.ResidentID, mra.StartDate.Max() };
from ra in RoomAssignments
join r in Rooms on ra.RoomID equals r.ID
join p in Persons on ra.ResidentID equals p.ID
where ra.ResidentID == maxQuery.ResidentID
where ra.StartDate == maxQuery.StartDate
orderby ra.ResidentID, ra.StartDate descending
select new {ra.ResidentID, ra.RoomID, r.Number, ra.StartDate, p.FacilityID}

Answer 1

按照我的 LINQ to SQL 配方，如果您只遵循 SQL，转换非常简单。唯一棘手的部分是将范围变量从最大开始日期的子查询join到与字段名称匹配的RoomAssignments的新匿名对象。

var maxQuery = from mra in RoomAssignments
group mra by mra.ResidentID into mrag
select new { ResidentID = mrag.Key, MaxStart = mrag.Max(mra => mra.StartDate) };
var ans = from m in maxQuery
join ra in RoomAssignments on m equals new { ra.ResidentID, MaxStart = ra.StartDate }
join r in Rooms on ra.RoomID equals r.ID
join p in Persons on ra.ResidentID equals p.ID
join cs in ComplianceStage on p.ComplianceStageID equals cs.Id
orderby ra.EndDate descending
select new {
ra.ResidentID,
ra.RoomID,
r.Number,
ra.StartDate,
p.FacilityID
};