将 SQL 转换为 Linq ( "WHERE EXISTS" )

Question

谁能帮我把这个SQL转换成LINQ?

SELECT * FROM Room_Type b
WHERE Exists(
 SELECT *
 FROM Room a 
 WHERE a.RoomId NOT IN
  (SELECT r.RoomId 
   FROM Reservation r
   WHERE NOT (r.DepartureDate<='2015-02-16' OR r.ArrivalDate > '2015-02-20' )
  )
)

Answer 1

您可以使用join来简化查询:

SELECT * 
FROM Room_Type rt 
    JOIN room r 
         ON r.RoomTypeId = rt.Id
    JOIN Reseration res
        ON res.RoomId = r.Id
WHERE NOT (r.DepartureDate<='2015-02-16' OR r.ArrivalDate > '2015-02-20' )))

那么你可以这样使用linq:

DateTime departure = new DateTime(2015,02,16);
DateTime arrival = new DateTime(2015,02,20);
context.Reservation.Where(r.DepartureDate<= departure || r.ArrivalDate > arrival ).Select(res => res.Room.Room_Type)