convert sql to linq
19,716
Solution 1
One of good tool to convert SQL to Linq : Linqer
Try out this query
var q = (from r in Room
join v in Reservation on r.RoomID equals v.RoomID into outer
from o in outer.DefaultIfEmpty()
where !(o.Date_Check_Out<= startdate || o.Date_Check_In>=endDate)
&& v.cancel == 0 && v.ReservationID == null
select r);
Also check this :
See SQL to LINQ Tool existing thread.
If you decide to do it by hand, Linqpad should be useful.
You also like to see : SQL to LINQ ( Visual Representation ) some good exaple by graphical representation...
Solution 2
Something like:
DateTime startDate, EndDate;
var bookings = from r in Rooms
join v in Reservation
on r.RoomID equals v.RoomID into RoomReservation
from v in RoomReservation.DefaultIfEmpty()
where
(Date_Check_Out < startDate || Date_Check_In > endDate)
select r;
Solution 3
Maybe something like this:
DateTime StartDate=DateTime.Now;
DateTime EndDate=DateTime.Now;
var result= (
from r in Room
from v in Reservation
.Where(a=>
a.RoomID == r.RoomID
&&
!(
StartDate>=a.Date_Check_Out ||
EndDate <=a.Date_Check_In
)
&& a.cancel==false
).DefaultIfEmpty()
where v.ReservationID == null
select r
);
Author by
jonny
Updated on June 04, 2022Comments
-
jonny almost 2 years
I am trying to implement a Filter by Date on my hotel reservation project.
Could somebody help me please to convert this sql code to linq .
SELECT r.* FROM Room r LEFT JOIN Reservation v ON r.RoomID = v.RoomID AND NOT (@StartDate >= Date_Check_Out OR @EndDate <= Date_Check_In) AND v.cancel = 0 WHERE v.ReservationID IS NULL
-
jonny almost 12 yearsvhat is RoomReservation a table?
-
Dave almost 12 yearsIt's an intermediary data structure that was created by the "into" statement used to create the left join in conjunction with DefaultIfEmpty