c# - How to convert nested sql statement to linq to entities? -

i'm trying create basic room availability statement use linq entity framework. have 2 tables: 'room' including columns roomid/roomsize , 'booking' including bookingid/roomid/startdate/enddate.

i have got working sql statement:

select roomid, roomsize room roomid not in (select roomid booking              ('08/01/2015' >= [start] , '08/01/2015' <= [end]) or ('08/20/2015' >= [start] , '08/20/2015' <= [end])) 

i have got far linq entity statement:

var rooms = (from r in db.rooms                !(((from b in db.bookings                        (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 error @ last bracket before .contains(r.roomid) saying should have select statement can't seem working.

any suggestions welcome.

if reckon using lambdas better/easier please feel free suggest , example. i'm not familiar them myself.. yet.

thank you.

you can use linq !...any() sql not in(), :

var rooms = (from r in db.rooms                !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              });