使用实体框架重叠约会 [英] overlapping appointments using the entity framework

问题描述

我使用asp.net mvc的实体框架。 我有约会了startedat字段，endedat场和roomid场（称为SpaceConfigurationId）的列表，并想找到已被双重预订了一个给定的房间任命名单。这是罚款认为endedat是startedat后始终。

有4个案件将允许预约匹配：

1. 在任命开始的任命前B开始和结束的后任命B开始任用b结束前
2. 在任命开始的后任命B开始任用b两端和结束的约会b结束后
前
3. 在任命开始的任命前B开始和结束的约会b结束后
4. 在任命开始的后任命B开始任用b两端和结束的后任命B开始任用b两端
前前

我想满足所有这些要求任命名单。它看似简单，但其实是一个有点头脑的游戏。我看了一下相交，但似乎并没有取得任何进展。 我已经定义了以下变量，想返回一个列表。

 的IQueryable＆LT;任命＆GT;约会= Repository.ReadAppointments（）;
...插入code在这里...
返回appointments.ToList（）;
 

下面是一些SQL这实在是太慢了，但可能有助于勾勒出的问题

  SELECT COUNT（*）
从任命
    交叉连接的任命b
哪里
    不a.Id = b.Id
    和
    a.SpaceConfigurationId = b.SpaceConfigurationId
    和
    （
        （a.StartedAt＆LT; b.StartedAt和a.EndedAt＆GT; b.StartedAt和a.EndedAt＆LT; b.EndedAt）
        要么
        （a.StartedAt＆GT; b.StartedAt和a.StartedAt＆LT; b.EndedAt和a.EndedAt＆GT; b.EndedAt）
        要么
        （a.StartedAt＆LT; b.StartedAt和a.EndedAt＆GT; b.EndedAt）
        要么
        （a.StartedAt＆GT; b.StartedAt和a.StartedAt＆LT; b.EndedAt和a.EndedAt＆GT; b.StartedAt和a.EndedAt＆LT; b.EndedAt）
    ）
 

解决方案

1. 定义的索引上SpaceConfigurationId和日期列。

2. 有2种情况下，当约会不匹配：

   1. 当前结束湾
   2. 当经过了B。

所以..

  SELECT COUNT（*）
从任命
加入任命b
上
    （不a.Id = b.Id）
    和
    （a.SpaceConfigurationId = b.SpaceConfigurationId）
    和
    NOT（a.EndetAt＆LT; b.StartedAt）
    和
    NOT（a.StartedAt＆GT; b.EndetAt）
 

所以...

  repository.ReadAppointments（），（一=＆GT; repository.ReadAppointments（）。
     任何（B =＆GT;
         ！（b.ID == a.ID）及＆安培;
         （a.SpaceConfigurationId == b.SpaceConfigurationId）及＆安培;
         ！（a.EndetAt＆所述; b.StartedAt）及＆安培;
         ！（a.StartedAt＆GT; b.EndetAt）））。
     选择（T =＆GT; t.ID）.ToList（）;
 

有不产生相同的SQL。实际上它使用 EXISTS ，但适当的指标应该工作正常。

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)
    )

解决方案

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.

这篇关于使用实体框架重叠约会的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！