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
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...