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

select value u from AEDMEntities.Units as u WHERE
not exists (select value r from AEDMEntities.Reservations as r
where  r.Unit.unitID = u.unitID and r.status = 2 AND 
(datetime '2009-7-7 00:00'between r.rfrom and r.rto 
or datetime '2009-7-7 00:00' between r.rfrom and r.rto 
or r.rfrom between datetime '2009-7-7 00:00'and datetime '2009-7-27 00:00' 
or r.rto between datetime '2009-7-7 00:00' and datetime '2009-7-27 00:00'))
AND u.category=3

This is working. But I can't write -1 or +1 to subtract/add day like in sql!How to achieve that, I just need to make it parametrized. Unit is abstract class it has 2 derived classes app&rooms(table per type inheritance) so I need to return only apps or rooms depending on method input parameter any ideas welcome.

Answer 2

I end up using stored procedures...