overlapping appointments using the entity framework

Question

I am using the asp.net mvc with the Entity Framework. I have a list of appointments with a startedat field, an endedat field and a roomid field (called SpaceConfigurationId) and would like to find the list of appointments that have been double booked for a given room. It is fine to assume that endedat is always after startedat.

There are 4 cases to would allow an appointment to be matched:

1. appointment a starts before appointment b starts and ends after appointment b starts and before appointment b ends
2. appointment a starts after appointment b starts and before appointment b ends and ends after appointment b ends
3. appointment a starts before appointment b starts and ends after appointment b ends
4. appointment a starts after appointment b starts and before appointment b ends and ends after appointment b starts and before appointment b ends

I would like the list of appointments that meet any of those requirements. It seems simple but is actually a bit of a mind game. I had a look at intersect but didn't seem to get anywhere. I have the following variable defined and would like to return a list.

IQueryable<Appointment> appointments = Repository.ReadAppointments();
... insert code here ...
return appointments.ToList();

Here is some SQL which is really slow but may help to outline the problem

select COUNT(*)
from appointment a
    cross join appointment b
where
    not a.Id = b.Id
    AND
    a.SpaceConfigurationId = b.SpaceConfigurationId
    AND
    (
        (a.StartedAt < b.StartedAt and a.EndedAt > b.StartedAt and a.EndedAt < b.EndedAt)
        OR 
        (a.StartedAt > b.StartedAt and a.StartedAt < b.EndedAt and a.EndedAt > b.EndedAt)
        OR 
        (a.StartedAt < b.StartedAt and a.EndedAt > b.EndedAt)
        OR 
        (a.StartedAt > b.StartedAt and a.StartedAt < b.EndedAt and a.EndedAt > b.StartedAt and a.EndedAt < b.EndedAt)
    )

Answer 1

1. Define indexes on SpaceConfigurationId and date columns.

2. There are 2 cases when appointments don't match:

   1. When a ends before b.
   2. When a starts after b.

So..

select COUNT(*)
from appointment a
join appointment b
on
    (not a.Id = b.Id)
    AND
    (a.SpaceConfigurationId = b.SpaceConfigurationId)
    AND
    NOT (a.EndetAt < b.StartedAt)
    AND
    NOT (a.StartedAt > b.EndetAt)

So...

repository.ReadAppointments().Where(a => repository.ReadAppointments().
     Any(b => 
         !(b.ID == a.ID) && 
         (a.SpaceConfigurationId == b.SpaceConfigurationId) &&
         !(a.EndetAt < b.StartedAt) &&
         !(a.StartedAt > b.EndetAt))).
     Select(t => t.ID).ToList();

It doesn't generate the same SQL. It actually uses EXISTS, but with proper indexes should work ok.