Linq查询中的分组操作

Question

RowNum IMAGEID     SCANEDATE                COUNT
1      10000131    2012-07-04 00:00:00.000  1
2      10002626    2012-08-03 00:00:00.000  1
3      10003348    2012-09-06 00:00:00.000  1
4      10003589    2012-09-15 00:00:00.000  8
5      10003590    2012-05-15 00:00:00.000  8
6      10003591    2012-04-15 00:00:00.000  8
7      10003592    2012-03-15 00:00:00.000  8
8      10003595    2012-02-15 00:00:00.000  8
9      10003596    2012-09-15 00:00:00.000  8
10     10003598    2012-09-15 00:00:00.000  8
11     10003599    2012-09-15 00:00:00.000  8

我有一个以上的数据表，需要应用LINQ查询以获得以下结果：

1) 所有具有相同日期的图像ID应列在同一单元格中(以逗号分隔)，计数列应该包含每个日期各自的图像ID数量的计数。

 Scan Date   Image ID  Count


      11/27/2007 1001529,1001530,1001531,1001532,1001533,1001534,1001537,1001538,1001539,1001540,1001542    11

      11/20/2008 1002501,1002502,1002503,1002504,1002505,1002506,1002507,1002508,1002509,1002510,1002511,1002512,1002513,1002514,1002515,1002516,1002517,1002518,1002519,1002520,    20

      7/5/2011   1015237,1015238    2

      7/6/2011   1015248,1015249,1015259,1015260,1015286,1015287,1015288,1015289,1015290,1015291,1015292,1015293,1015294,1015295,1015296,1015297,1015347,1015348,1015358,1015359,    32
      1015370,1015371,1015381,1015382,1015396,1015397,1015410,1015411,1015412,1015413,1015429,1015430

      7/7/2011   1015444,1015445    2

请提供执行上述操作的查询语句。

- user2783853

Answer 1

按照SCANEDATE字段对组表行进行分组。然后通过选择扫描日期（组的键），将所有图像ID连接成字符串，并获取组中行数的计数来投射每个组的行：

table.AsEnumerable()
     .GroupBy(r => r.Field<DateTime>("SCANEDATE"))
     .Select(g => new {
          ScanDate = g.Key,
          Ids = String.Join(",", g.Select(r => r.Field<int>("IIMAGEID"))),
          Count = g.Count()
      });

- Sergey Berezovskiy

---

这个错误提示告诉我当前上下文中不存在变量 g。 - user2783853

@user2783853 很抱歉，lambda表达式中的箭头符号有误。应该是 g => new {.. 而不是 g = new { ...。 - Sergey Berezovskiy

Answer 2

var results = from p in tabel
              group p.IMAGEID  by p.SCANEDATE into g
              select new { SCANEDATE = g.Key, IMAGEID = g.ToList() };

或者作为非查询表达式：

var results = tabel.GroupBy(p => p.SCANEDATE , p => p.IMAGEID ,
                         (key, g) => new { SCANEDATE = key, IMAGEID = g.ToList() });

基本上，该组的内容（当视为 IEnumerable<T>）是给定键的投影中存在的任何值序列（在此示例中为 p.car）。

- sourabh devpura