convert SQL to LINQ not working

Question

I want to convert this SQL code to LINQ. Here is my SQL code:

SELECT Rooms.RoomName AS RoomNo, Beds.BedName AS Beds, Rooms.RoomType, ISNULL(CheckIn.CheckIntatus,'') AS Status 
FROM CheckIn
INNER JOIN GuestBeds ON CheckIn.GuestBedId = GuestBeds.Id
AND (CheckIn.CheckInStatus = 1 OR CheckIn.CheckIntatus = 2 OR CheckIn.CheckSIntatus = 3) 
RIGHT JOIN Beds ON GuestBeds.BedId = Beds.Id
INNER JOIN Rooms ON Beds.RoomId = Rooms.Id
LEFT JOIN Guests ON CheckIn.GuestId = Guests.Id
WHERE Beds.Active = 1 AND Rooms.Active = 1
ORDER BY RoomName, Beds

It works well which means it shows all the RoomName with CheckInStatus. If the Room is not presence in CheckIn table, ot will return the status as Null. So I want to convert the code to LINQ. SO here is my LINQ code:

from b in Beds 
join w in Rooms on b.RoomsId equals w.Id
where (a.CheckInStatus == 3 || a.CheckInStatus == 1 || a.CheckInStatus == 2)
join p in GuestBeds on b.Id equals p.BedId
join a in CheckIn on p.Id equals a.GuestBedId 
join t in Guests on a.GuestId equals t.Id
where b.Active == true && w.Active == true
orderby w.RoomName
select new
{
    RoomName = w.RoomName,
    BedName = b.BedName,
    Status = a.CheckInStatus
}

It didnt worked like the first code. It only show the data which contain CheckInStatus. I want it to show all the RoomName inside Room database

Looks like your SQL is using LEFT and RIGHT joins, but your LINQ is not taking this into account. See if this helps: https://stackoverflow.com/questions/4497086/linq-left-join-and-right-join — Randy Slavey, Jan 25 '18 at 05:37
Is there some reason you didn't copy/paste your SQL? It has errors in it... — NetMage, Jan 25 '18 at 22:30

NetMage · Answer 1 · 2018-01-25T22:47:02.620

Normally I would post some rules for converting SQL to LINQ but this is complicated enough I think I'd need to make new rules. I commented out the references to Guests because as a LEFT JOIN it has no bearing on the answer.

Pull out the WHERE on individual tables and make them sub-queries:

var ActiveBeds = Beds.Where(b => b.Active == 1);
var ActiveRooms = Rooms.Where(r => r.Active == 1);

In LINQ, a RIGHT JOIN must be done by flipping the join to be a left join, so we will create the two sides as sub-queries.

Left side of RIGHT JOIN:

Translate the JOIN conditions that aren't part of an equi-join into a LINQ where clause on the appropriate tables (alternately this could be a subquery as above). The LEFT JOIN becomes a LINQ join/from ... DefaultIfEmpty() phrase, but as noted above isn't needed.

var CheckInsGuestBedsGuests = from c in CheckIn
                              where (c.CheckInStatus == 1 || c.CheckInStatus == 2 || c.CheckInStatus == 3)
                              join gb in GuestBeds on c.GuestBedId equals gb.Id
                              //join g in Guests on c.GuestId equals g.Id into gj
                              //from g in gj.DefaultIfEmpty()
                              select new { c, gb /*, g */ };

Right side of RIGHT JOIN:

The other side of the RIGHT JOIN includes an INNER JOIN so put them together in a sub-query:

var ActiveBedsRooms = from b in ActiveBeds
                      join r in ActiveRooms on b.RoomId equals r.Id
                      select new { b, r };

Finally, flip the sub-queries to create a left join using the same idiom as above:

var ans = from br in ActiveBedsRooms
          join cgbg in CheckInsGuestBedsGuests on br.b.Id equals cgbg.gb.BedId into cgbgj
          from cgbg in cgbgj.DefaultIfEmpty()
          select new {
              RoomNo = br.r.RoomName,
              Beds = br.b.BedName,
              br.r.RoomType,
              Status = cgbg.c.CheckInStatus
          };

NOTE: If you were not using LINQ to SQL, the Status expression would fail when cgbg is null and you would need

              Status = cgbg?.c.CheckInStatus

but unfortunately LINQ to SQL/EF doesn't handle the null conditional operators yet.

BTW, nice query - brings back memories of when I used to write hotel front desk software :)

convert SQL to LINQ not working

1 Answers1