时间戳

首页 > 解决方案 > Linq to SQL C#: items between dates

c# - Linq to SQL C#: items between dates

问题描述

I have 2 tables in my SQL server 2012:

Errors (id, cityID, centerID, date)

InspectionVisits (id, cityID, centerID, datePerformed)

I am trying to get the number of errors between inspection visits to see if there is an improvement in the center with the specific centerID and build a chart.

This is my code so far but I can't find out how I can write the where clause to get the number of errors between these inspection visits:

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()
                  };

Sample Case Something like: 5 errors between Inspection_Visit_1 and Inspection_Visit_2, 2 errors between Inspection_Visit_2 and Inspection_Visit_3 and 1 error between Inspection_Visit_3 and today.

EDIT

Maybe it could work if I show queries per day and mark only the inspection visits in the chart's x axis.

标签： c#linq-to-sql

解决方案

我认为这在客户端最容易处理。

首先，您要获取有趣的内容InspectionVisits并按日期排序，然后转换为 anEnumerable以将它们拉到客户端：

var orderedIVs = InspectionVisits.Where(iv => iv.cityID == ctid && iv.centerID == centerid).Select(iv => iv.dateperformed).OrderBy(ivdp => ivdp).AsEnumerable();

现在使用一个沿 处理的扩展方法Enumerable来计算运行值（之所以调用它是Scan因为它是在 APL Scan 运算符之后建模的，就像Aggregate返回所有中间值的 an 一样）：

// TKey combineFn(T prev, T cur)
// TKey lastKeyFn(T cur)
public static IEnumerable<TResult> Scan<T, TResult>(this IEnumerable<T> src, Func<T, T, TResult> combineFn, Func<T, TResult> lastKeyFn) {
    using (var srce = src.GetEnumerator()) {
        if (srce.MoveNext()) {
            var prev = srce.Current;

            while (srce.MoveNext()) {
                yield return combineFn(prev, srce.Current);
                prev = srce.Current;
            }
            yield return lastKeyFn(prev);
        }
    }
}

您可以计算检查访问的周期：

var IVPeriods = orderedIVs.Scan((prev, cur) => new { Begin = prev, End = cur }, cur => new { Begin = cur, End = DateTime.Now });

最后，您可以使用期间计算Errors每个期间之间发生的时间：

var errorsPerIV = IVPeriods.Select(ivp => new { Day = ivp.Begin.Date, Count = Errors.Where(e => ivp.Begin <= e.date && e.date <= ivp.End).Count() });

如果您想在服务器端处理这个，您必须将InspectionVisits表连接到自身，以便您可以创建期间。我没有用 SQL server 测试过这个：

var orderedIVs = InspectionVisits.Where(iv => iv.cityID == ctid && iv.centerID == centerid).Select(iv => iv.dateperformed).OrderBy(ivdp => ivdp);

var IVPeriods = (from ivb in orderedIVs
                from ive in orderedIVs
                where ivb < ive
                group ive by ivb into iveg
                 select new { Begin = iveg.Key, End = iveg.OrderBy(iv => iv).First() })
                .Concat((from ivb in orderedIVs orderby ivb descending select new { Begin = ivb, End = DateTime.Now }).Take(1));

var errorsPerIV = IVPeriods.Select(ivp => new { Day = ivp.Begin.Date, Count = Errors.Where(e => ivp.Begin <= e.date && e.date <= ivp.End).Count() });

推荐阅读