[FIXED] How to translate Linq/SQL to Lambda in c#

Issue

How would one translate the Linq code below to Lambda type of query?

        var domainModel = (
                            from Hosps in _ctx.Hospital
                            from Room in _ctx.SickRoom
                                     .Where(x => x.HospitalId == Hosps.HospitalId)
                                     .GroupBy(x => x.HospitalId)
                                     .Select(x => new
                                     {
                                         HospitalId = x.Key,
                                         NumberOfFloors = x.Max(y => y.FloorId),
                                         NumberOfRooms = x.Count()
                                     })
                                     .OrderBy(x => x.HospitalId)
                            select new 
                            {
                                HospitalId = Hosps.HospitalId,
                                City = Hosps.City,
                                HospitalImage = Hosps.HospitalImage,
                                NumberOfFloors = Room.NumberOfFloors,
                                NumberOfApartments = Room.NumberOfRooms
                            }
                        ).ToList();

If it helps, the SQL query for the Linq expression above is:

SELECT [r].[HospitalId]
      ,[r].[City]
      ,[r].[HospitalImage]
      ,[t].[c] AS [NumberOfFloors]
      ,[t].[c0] AS [NumberOfRooms]
FROM [Hospital] AS [r] 
INNER JOIN (
    SELECT [a].[HospitalId], MAX([a].[FloorId]) AS [c], COUNT(*) AS [c0]
    FROM [SickRoom] AS [a]
    GROUP BY [a].[HospitalId]
) AS [t] ON [r].[HospitalId] = [t].[HospitalId]

Solution

Assuming you have a proper model taking relations into consideration, it would be like:

var domainModel = _ctx.Hospital.Select(h => 
                        new
                        {
                            HospitalId = h.HospitalId,
                            City = h.City,
                            HospitalImage = h.HospitalImage,
                            NumberOfFloors = h.SickRooms.Max(sr => sr.FloorId),
                            NumberOfApartments = h.SickRooms.Count()
                        })
                        .ToList();

Note that your SQL suggests that in your model there is a navigation to a hospital's SickRooms (h.SickRooms).

Answered By - Cetin Basoz

Answer Checked By - David Marino (PHPFixing Volunteer)