CODEWITHSUNDEEP
c#linqentity-frameworknested
user3723134
user3723134

Reputation: 27

How to convert nested sql statement to linq to entities?

I'm trying to create a basic room availability statement to use with linq to entity framework. I have two tables: 'Room' including columns RoomID/RoomSize and 'Booking' including BookingID/RoomID/StartDate/Enddate.

I have got a working sql statement: 

SELECT RoomID, RoomSize from Room
where RoomID NOT IN (SELECT RoomID from booking
             where ('08/01/2015' >= [start] AND '08/01/2015' <= [end]) OR ('08/20/2015' >= [start] AND '08/20/2015' <= [end]))

I have got this far with the linq to entity statement: 

var rooms = (from r in db.Rooms  
             where !(((from b in db.Bookings
                       where (startDate >= b.StartDate && endDate <= b.EndDate) || (endDate >= b.StartDate && endDate <= b.EndDate)).Contains(r.RoomID))     
                       select new AvailableRoom
                       {
                           ID = r.RoomID,
                           Size = r.RoomSize
                       });

I get an error at the last bracket before .Contains(r.RoomID) saying I should have a select statement but I just can't seem to get it working.

Any suggestions would be welcome.

If you reckon using lambdas would be better/easier please feel free to suggest and example. I'm just not too familiar with them myself.. yet.

Thank you.

Upvotes: 0

Views: 53

Answers (1)

har07
har07

Reputation: 89285

You can use LINQ !...Any() for the SQL NOT IN(), like so :

var rooms = (from r in db.Rooms  
             where !db.Bookings
                      .Where(b => (startDate >= b.StartDate && endDate <= b.EndDate) 
                                    || 
                                  (endDate >= b.StartDate && endDate <= b.EndDate)
                            )
                      .Any(b => b.RoomID == r.RoomID)
             select new AvailableRoom
             {
               ID = r.RoomID,
               Size = r.RoomSize
             });

Upvotes: 2

Related Questions