Linq查询以找到日期范围

Question

我有一张表格，记录了人员开始项目的时间：

PersonId
ProjectId
StartDate

我希望使用linq（linq-to-entities）来获取像这样的结果集。

PersonId
ProjectId
StartDate
EndDate

当按照开始日期排序时，EndDate代表他们下一个项目的开始日期，如果没有更近的项目，则为null。

这是我所做的：

context.PersonProjects.Select(pp => new {
    pp.PersonId,
    pp.ProjectId,
    pp.StartDate,
    EndDate = context.PersonProjects.Where(pp2 => pp2.PersonId == pp.PersonId && pp2.StartDate > pp.StartDate).OrderBy(pp2 => pp2.StartDate).Select(pp2 => pp2.StartDate).FirstOrDefault()
})

这样做有更好的性能优化方法吗？

- Adam

Answer 1

更高效的方法（但不够美观）是将所有内容读入按开始日期排序的列表中，然后遍历该列表，并获取下一项（如果有）的开始日期。

// You need a named class in order to make this work
class PersonProject {
    int PersonId {get;set;}
    int ProjectId {get;set;}
    DateTime StartDate {get;set;}
    DateTime EndDate {get;set;}
}
...
// Run your query, and put the results in a list
var listOfProjects = context
    .PersonProjects
    .OrderBy(pp => pp.StartDate)
    .Select(pp => new PersonProject {
        PersonId = pp.PersonId,
        ProjectId = pp.ProjectId,
        StartDate = pp.StartDate
    }).ToList();
// Now walk through the list, setting the end time to the start of the next item
for (int i = 0 ; i < listOfProjects.Length-1 ; i++) {
    listOfProjects[i].EndDate = listOfProjects[i+1].StartDate;
}

这个解决方案的查询和“修正”部分都是线性的，因此性能与读取PersonProject表本身一样好。

- Sergey Kalinichenko

---

谢谢，这很有道理。这个特定的表大约有30万行，所以我不确定是否要将它们全部加载到内存中。通常，我只想得到这些结果的聚合（例如，找到在一段时间内参与项目的人数），这意味着这只是另一个查询的基础查询，因此保持它IQueryable似乎是理想的。 - Adam

@Adam 如果你把“修复循环”放在一个单独的方法中，该方法接受一个正确排序的列表，那么你就可以保留IQueryable，并且只有在所需子集在内存中时才执行修复部分。 - Sergey Kalinichenko

没错，直到修复循环之后我才能使用“EndDate”来过滤结果。有时候我可能会需要这样做。我可能需要进行一些测试，看看这种方法与其他方法相比如何。 - Adam

@Adam 在这种情况下，你最好完全采用时间方式，并将 EndDate 存储在数据库中。这会增加一些维护工作，但大大简化了查询，并且还可以让你直接在数据库中执行非常高效的“截止日期”查询。 - Sergey Kalinichenko