时间戳

首页 > 解决方案 > 使用 GroupBy() 和 Sum() 的 Linq 查询和未分组的返回值

c# - 使用 GroupBy() 和 Sum() 的 Linq 查询和未分组的返回值

问题描述

尝试编写 linq 查询以对某些数据进行分组并求和时，我收到以下错误：

CS1061“CM_Records”不包含“Sum”的定义，并且找不到接受“CM_Records”类型的第一个参数的扩展方法“Sum”

var results = (from r in CM_Records
      join sub in CM_Records on r.Id equals sub.Id
      select new {R = r, S = sub}
      into joined
      group joined by new
      {
         label = joined.R.ServiceDateTime.Value.Month + "/" + joined.R.ServiceDateTime.Value.Day + "/" + joined.R.ServiceDateTime.Value.Year,
         joined.R.CategoryId
      }
      into grouped
      select grouped.Select(g =>
         new
         {
            grouped.Key.label,
            grouped.Key.CategoryId,
            Value = g.S.Sum(x => int32.Parse(x.Value)), //This is the line that fails
            //Value = g.S.Value, //Uncomment this line and comment the line above to get the following output.
            ServiceTime = Convert.ToDateTime(g.S.ServiceDateTime).ToShortTimeString()
         })
   )
   .SelectMany(x => x);

我知道 Sum Requires the object to be IQueryable，只是不确定如何在这个查询中完成它而不破坏其他东西。请记住，此查询被设置为变量，然后结合使用以在 C# 中生成 JSON 对象。

不带 SUM 的电流输出：

Label    Cat.  Value ServiceTime
1/1/2017    439960  121 9:00 AM
1/1/2017    439960  131 5:00 PM
1/1/2017    439960  213 11:45 AM
1/1/2017    439960  210 10:20 AM
1/1/2017    439961  143 9:30 AM
1/1/2017    439994  92  9:30 AM
1/1/2019    439989  0   7:00 PM
1/1/2020    439968  172 7:00 PM
1/10/2018   439968  124 7:00 PM
1/10/2018   439969  120 7:00 PM

我需要将上述结果按标签列分组并将值相加。

标签： c#sqljsonlinq

解决方案

试试这个查询：

var results = (from r in CM_Records
               join sub in CM_Records on r.Id equals sub.Id
               select new {R = r, S = sub}
               into joined
               group joined by new
               {
                  label = joined.R.ServiceDateTime.Value.Month + "/" + joined.R.ServiceDateTime.Value.Day + "/" + joined.R.ServiceDateTime.Value.Year,
                  joined.R.CategoryId
               }
               into grouped
               select new
               {
                  grouped.Key.label,
                  grouped.Key.CategoryId,
                  Value = grouped.ToList().Select(x => x.S.value).Sum()
               }).ToList();

包括 ServiceTime 没有多大意义，除非您决定选择最大值、最小值或类似的值。

推荐阅读