Searching free accomodation

Question

I need help with a query? I have 2 tables reservations & units among others. Table reservations has columns ResId,rfrom(datetime),rto(datetime),status(int),UnitID(foreign key). Status 2 means its confirmed. I need to get all free units in requested period, query needs to return only that units for which there aren't confirmed reservations (status ==2) in requested period(not exist). I'm working with entity framework so it should be eSQL query(other option is using stored procedure but i want avoid that). Database is sql express 2005. Also query should filter units depending on values from table units but that's not a problem. I can do that with linq on results of query(multiple where statements).

edit: This query is working:

    select * from Units where
    not exists (select *
        from Reservations
        where Reservations.unitID = Units.unitID
        and Reservations.status = 2
        and (@datefrom between Reservations.rfrom and Reservations.rto-1
        or @dateto between Reservations.rfrom+1 and Reservations.rto
        or rfrom between @datefrom and @dateto-1 
        or rto between @datefrom+1 and @dateto))
and Units.category=@cat

How would look in entity sql? could i do it with linq? entity names are same as tables.

Answer 1

I end up using stored procedures...