C# Linq问题：如何识别和计算集合中的项目数量

Question

我有一个朋友正在使用C# Linq报告以下表格：

varchar(50) name
datetime    when

客户希望能够选择要汇总的列，例如今年的总数、去年的总数、两周前的总数等等。每个列都有一些开始和结束时间，它们会用计数进行汇总。我为他快速编写了一个查询语句：

select A.name as [Name],

    (select count(*) from mytable B where A.name = B.name and 
     datepart(yy, when) = datepart(yy,getdate())) as [This Year],

    (select count(*) from mytable B where A.name = B.name and
     datepart(yy, when) = (datepart(yy,getdate()) - 1)) as [Last Year]

from (select distinct name from mytable) A

我还建议他将每个计算字段都设置为代理，这样他就可以在查询之外包含计算混乱的内容。假设每个委托都有一个开始和结束日期，计算每个不同名称发生了多少次命中，有人想尝试一下Linq c#代码会是什么样子吗？

- Zachary Scott

Answer 1

很简单的翻译：

DateTime Now = DateTime.Now;

DateTime thisYearStart = new DateTime(Now.Year, 1, 1);
DateTime thisYearEnd = thisYearStart.AddYears(1);
DateTime lastYearStart = thisYearStart.AddYears(-1);
DateTime lastYearEnd = thisYearStart

var query = dc.MyTable
  .GroupBy(a => a.Name)
  .Select(g => new
  {
    Name = g.Key,
    ThisYearCount = g
      .Count(b => thisYearStart <= b.When && b.When < thisYearEnd)
    LastYearCount = g
      .Count(b => lastYearStart <= b.When && b.When < lastYearEnd)
  });

顺便提一下，这是我要翻译的SQL重构后的代码：

SELECT A.Name as [Name],
  SUM(CASE WHEN @start1 <= A.When AND A.When < @end1 THEN 1 ELSE 0 END) as ThisYear,
  SUM(CASE WHEN @start2 <= A.When AND A.When < @end2 THEN 1 ELSE 0 END) as LastYear
FROM MyTable A
GROUP BY A.Name

- Amy B

Answer 2

这里有一种通用的方法，可用于任何表格和日期字段，并返回您可以计数的组。您需要多次运行它，以获取所需数据的每个日期范围。

static IQueryable<IGrouping<TKey, TSource>> GroupWithinDates<TSource, TKey>(
    this IQueryable<TSource> source,
    Expression<Func<TSource, TKey>> keySelector,
    Expression<Func<TSource, DateTime>> dateSelector,
    DateTime startDate,
    DateTime endDate
)
{
    return source
        .Where(item => dateSelector(item) >= startDate
                       && dateSelector(item) < endDate)
        .GroupBy(keySelector)
}

// Usage after setting startDate and endDate
var groups = mytable.GroupWithinDates(
               A => A.name, A => A.when, startDate, endDate)
// You can then transform to Name and Count
var groupCounts = groups.Select(g => new {Name = g.Key, Count = g.Count()};

- DRBlaise