convert sql to linq

Question

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

Which bit are you having trouble with? – Jon Skeet May 04 '12 at 07:29 — Jon Skeet, May 04 '12 at 07:29

score 10 · Accepted Answer · edited May 23 '17 at 12:31

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

score 2 · Answer 2 · answered May 04 '12 at 07:32

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;

answered May 04 '12 at 07:32

Dave

3,581
1
22
25

vhat is RoomReservation a table? – jonny May 04 '12 at 07:38
It's an intermediary data structure that was created by the "into" statement used to create the left join in conjunction with DefaultIfEmpty – Dave May 04 '12 at 07:41

score 1 · Answer 3 · answered May 04 '12 at 07:43

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
    );

convert sql to linq

3 Answers3