vb.net datatable asenumerble获取不同的值并对分组重复行进行计数

Question

我有一个名为dtDealer的数据表，其中有两列，分别是Customer, Year。我正在尝试创建一个名为dtdistinct的新数据表，其中有一个名为multi的额外列，显示重复行数的计数…

dtDealer

客户|年份

---

2012AAA

BBB 2011

AAA 2012

BBB 2011

BBB 2011

BBB 2012

dtmulti

Customer | Year | multi

---

AAA 2012 2

BBB 2011 3

BBB 2012 1

尝试使用asenumerable，但不工作，请帮助

Answer 1

AsEnumerable可以工作。我怀疑你对分组有问题。我按Tuple

分组

Dim dealers = {(Customer:= "AAA", Year:= 2012),
(Customer:= "BBB", Year:= 2011),
(Customer:= "AAA", Year:= 2012),
(Customer:= "BBB", Year:= 2011),
(Customer:= "BBB", Year:= 2011),
(Customer:= "BBB", Year:= 2012)}
Dim dtDealer As New DataTable()
dtDealer.Columns.Add("Customer")
dtDealer.Columns.Add("Year")
For Each dealer In dealers
Dim row = dtDealer.NewRow()
row("Customer") = dealer.Customer
row("Year") = dealer.Year
dtDealer.Rows.Add(row)
Next
Console.WriteLine($"Customer | Year")
For Each row In dtDealer.AsEnumerable
Console.WriteLine($"{row("Customer")}{vbTab}{row("Year")}")
Next
Dim dtMulti As New DataTable()
dtMulti.Columns.Add("Customer")
dtMulti.Columns.Add("Year")
dtMulti.Columns.Add("Multi")
dtMulti = dtDealer.AsEnumerable().
GroupBy(Function(dealer) (dealer("Customer"), dealer("Year"))).
Select(Function(g)
Dim newRow = dtMulti.NewRow()
newRow("Customer") = g.First.Item("Customer")
newRow("Year") = g.First.Item("Year")
newRow("Multi") = g.Count()
Return newRow
End Function).
CopyToDataTable()
Console.WriteLine($"Customer | Year | Multi")
For Each row In dtMulti.AsEnumerable
Console.WriteLine($"{row("Customer")}{vbTab}{row("Year")}{vbTab}{row("Multi")}")
Next

客户|年份2012年
AAA
BBB 20112012年
AAA
BBB 2011
BBB 2011
BBB 2012
客户|年|多
中华医学会2012
BBB 2011
BBB 2012 1