C#使用LINQ对DataTable按多列进行分组

Question

我有一个datatable，其中包含三列：string, DateTime和decimal。我想按照string和decimal列进行分组，并对分组后的行中的decimal值求和。我知道如何做求和部分，但如何在datatable中分组两列呢？

这是目前我的代码，但它不能正常工作：

var newSort = from row in objectTable.AsEnumerable()
              group row by new {ID = row.Field<string>("resource_name"), time1 = row.Field<DateTime>("day_date")} into grp
              orderby grp.Key
              select new
              {
                 resource_name1 = grp.Key.ID,
                 day_date1 = grp.Key.time1,
                 Sum = grp.Sum(r => r.Field<Decimal>("actual_hrs"))
              };

- Sam

---

编译器认为所有对象都是相同的，因此每一行都被分组了。你的意思是所有对象都被放入一个大组中，所以最终在 newSort 集合中只有一个项目？ - StriplingWarrior

是的，newSort仅包含数据表的最后一个元素。 - Sam

查询看起来对我来说是正确的。你能检查并重新检查你的输入吗？ - StriplingWarrior

Answer 1

我认为你没有完全告诉我们整个情况。除了无法在匿名类型中使用orderby（你提供的代码无法通过编译）之外，你的查询应该按照你想要的方式工作。我在LINQPad中输入了以下代码：

var objectTable = new DataTable();
objectTable.Columns.Add("resource_name",typeof(string));
objectTable.Columns.Add("day_date",typeof(DateTime));
objectTable.Columns.Add("actual_hrs",typeof(decimal));
objectTable.Rows.Add(1, DateTime.Today, 1);
objectTable.Rows.Add(2, DateTime.Today, 2);

var newSort = from row in objectTable.AsEnumerable()
            group row by new {ID = row.Field<string>("resource_name"), time1 = row.Field<DateTime>("day_date")} into grp
            select new
                {
                    resource_name1 = grp.Key.ID,
                    day_date1 = grp.Key.time1,
                    Sum = grp.Sum(r => r.Field<Decimal>("actual_hrs"))
                };
newSort.Dump();

...我得到了以下结果：

resource_name1 | day_date1            | Sum
1              | 7/1/2011 12:00:00 AM | 1 
2              | 7/1/2011 12:00:00 AM | 2 

- StriplingWarrior

---

想知道如何根据两个其他列的最小值结合来从上述每个组中提取一行？ - Si8

@Si8: grp.OrderBy(g => g.col1 + g.col2).FirstOrDefault() 是什么意思？ - StriplingWarrior

Answer 2

使用这段代码。

var newSort = from row in objectTable.AsEnumerable()
          group row by new {ID = row.Field<string>("resource_name"), time1 =    row.Field<DateTime>("day_date")} into grp
          orderby grp.Key
          select new
          {
             resource_name1 = grp.Key.ID,
             day_date1 = grp.Key.time1,
             Sum = grp.Sum(r => Convert.ToDecimal(r.ItemArray[2]))
          };

- ar.gorgin

Answer 3

对于那些想要在Vb.net中找到解决方案的人，这里有一个示例：

Dim workTable As DataTable = New DataTable("Customers")

Dim workCol As DataColumn = workTable.Columns.Add("ID", Type.GetType("System.Int32"))
workTable.Columns.Add("Total", Type.GetType("System.Decimal"))
workTable.Columns.Add("Compra", Type.GetType("System.Decimal"))

Dim row As DataRow = workTable.NewRow()
row("id") = 2
row("total") = 1.5
row("compra") = 3
workTable.Rows.Add(row)
row = workTable.NewRow()

row("id") = 1
row("total") = 1.5
row("compra") = 3.3999999999999999
workTable.Rows.Add(row)
row = workTable.NewRow()
row("id") = 1
row("total") = 1.5
row("compra") = 5
workTable.Rows.Add(row)


Dim detalles As IEnumerable(Of DataRow) = workTable.AsEnumerable()

Dim query = From detalle In detalles.AsEnumerable() _
            Group detalle By grupoClave = New With _
                                        { _
                                            Key .C2 = detalle("id"), _
                                            Key .C4 = detalle("total")} Into g = Group _
                                    Select New With _
                                    { _
                                        .Col2 = g(0).Field(Of Integer)("id"), _
                                        .Col3 = g(0).Field(Of Decimal)("total"), _
                                        .Col4 = g.Sum(Function(fact) fact.Field(Of Decimal)("compra")) _
                                    }

For Each p In query
    Console.WriteLine((p.Col2 & p.Col3 & p.Col4))
Next

- user3837750