vb.net datatable asenumerble get distinct vlues and count grouped duplicate rows

Question

I have a datatable called dtDealer with 2 columns called Customer, Year. I am trying to make a new datatable called dtdistinct which has an extra column called multi which shows the count for the number of duplicate rows...

dtDealer

Customer | Year

---

AAA 2012

BBB 2011

AAA 2012

BBB 2011

BBB 2011

BBB 2012

dtmulti

Customer | Year | multi

---

AAA 2012 2

BBB 2011 3

BBB 2012 1

tried with asenumerable but not working please help

Answer 1

AsEnumerable does work. I suspect you had an issue with grouping. I grouped by a 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

Customer | Year
AAA 2012
BBB 2011
AAA 2012
BBB 2011
BBB 2011
BBB 2012
Customer | Year | Multi
AAA 2012 2
BBB 2011 3
BBB 2012 1