Need help converting SQL into 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

I'm trying to figure out how to convert this to C# using LINQ. I'm brand new with C# and LINQ and can't get my subquery to fire correctly. Any chance one of you wizards can turn the lights on for me?

Update-----------------

I think I've got the jist of it, but am having trouble querying for the max startdate:

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}

You might want to read "[*What is the syntax for an inner join in LINQ to SQL?*](https://stackoverflow.com/q/37324/1364007)" — Wai Ha Lee, Jun 14 '18 at 14:32
You might find my [SQL to LINQ Recipe](https://stackoverflow.com/questions/49245160/sql-to-linq-with-multiple-join-count-and-left-join/49245786#49245786) helpful as well. — NetMage, Jun 14 '18 at 22:33

score 0 · Answer 1 · answered Jun 14 '18 at 22:44

Following my LINQ to SQL Recipe, the conversion is pretty straight forward if you just follow the SQL. The only tricky part is joining the range variable from the subquery for max start date to a new anonymous object from RoomAssignments that matches the field names.

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

Need help converting SQL into LINQ

1 Answers1