Linq to SQL C#：日期之间的项目

Question

我的 SQL Server 2012 中有 2 个表：

Errors (id, cityID, centerID, date)

InspectionVisits (id, cityID, centerID, datePerformed)

我正在尝试获得errors之间的数量，inspection visits以查看中心是否有特定的改进centerID并构建图表。

到目前为止，这是我的代码，但我不知道如何编写 where 子句来获取这些检查访问之间的错误数：

var errorsPerIV = from e in dc.Errors

                  where e.cityID == ctid && e.centerID == centerid 

                  group e by e.date.Date into g

                  join iv in dc.InspectionVisits on g.FirstOrDefault().cityID equals iv.cityID

                  where iv.centerID == g.FirstOrDefault().centerID

                  select new

                  {

                      Day = g.Key.Day + "/" +

                            g.Key.Month + "/" +

                            g.Key.Year,

                      Errors = g.Count()

                  };

示例案例 类似于：Inspection_Visit_1 和Inspection_Visit_2 之间有5 个错误，Inspection_Visit_2 和Inspection_Visit_3 之间有2 个错误，以及Inspection_Visit_3 和今天之间有1 个错误。

编辑

如果我每天显示查询并仅在图表的 x 轴上标记检查访问，它可能会起作用。

Answer 1

我不确定是否有更好的方法，但您可以执行以下操作假设你有一个类public class Summary{&nbsp; &nbsp; public DateTime? PreviousInspection;&nbsp; &nbsp; public DateTime? NextInspection;&nbsp; &nbsp; public int Errors;}然后你可以通过查询来获得大部分信息var errorsPerIV = (from e in dc.Errors&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; where e.cityID == ctid && e.centreID == centreid&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; // Find the date of the previous inspection (if any)&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; let previousInspection = (from i in dc.InspectionVisits where i.cityID == e.cityID && i.centreID == e.centreID && i.datePerformed <= e.date orderby i.datePerformed descending select i.datePerformed).FirstOrDefault()&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;// Find the date of the next inspection (if any)&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; let nextInspection&nbsp; &nbsp; &nbsp;= (from i in dc.InspectionVisits where i.cityID == e.cityID && i.centreID == e.centreID && i.datePerformed >&nbsp; e.date orderby i.datePerformed ascending&nbsp; select i.datePerformed).FirstOrDefault()&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; group e by new { previousInspection , nextInspection } into results&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; orderby results.Key.previousInspection&nbsp;&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; select new Summary&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; {&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; PreviousInspection = results.Key.previousInspection,&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; NextInspection&nbsp; &nbsp; &nbsp;= results.Key.nextInspection ,&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; Errors&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;= results.Count()&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; })&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; .ToList();但是，如果两次访问之间没有错误，那么这些访问将不会出现在您的列表中，因此您需要查找所有访问并查看是否有缺失，即类似var inspectionsDates = (from i in InspectionVisits where i.cityID == ctid && i.centreID == centreid orderby i.datePerformed select i.datePerformed).ToList();&nbsp; &nbsp; for(int i=0; i< inspectionsDates.Count-1; i++)&nbsp; &nbsp; {&nbsp; &nbsp; &nbsp; &nbsp; if (!errorsPerIV.Any(a=>a.PreviousInspection == inspectionsDates[i]))&nbsp; &nbsp; &nbsp; &nbsp; {&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; errorsPerIV.Add(new Summary() { PreviousInspection = inspectionsDates[i], NextInspection = inspectionsDates[i + 1], Errors = 0});&nbsp; &nbsp; &nbsp; &nbsp; }&nbsp; &nbsp; }