CODEWITHSUNDEEP
vb.netlinqdatatable
BobNoobGuy
BobNoobGuy

Reputation: 1645

LinQ DataTable Group By

I have a datatable in vb.net application. the data table is looking like this:

State Level City
AL     T     Arlton
AL     T     Brton
AL     T     Rurton
CO     T     Dver
CO     T     Crodo
WA     T     Blain
WA     T     Bwelham

I would like to group the state and do a loop I tried the following but is not working as expected. please help

what I tried so far: 

Dim result = From r In dt Group r By state = r(0) Into Group Select Group
For Each r In result
    Console.WriteLine(r("State"))
Next

The output from the loop should be 

AL
CO
WA

Thank you

Upvotes: 0

Views: 1637

Answers (2)

Shyju
Shyju

Reputation: 218732

You cannot call GroupBy on a datatable. So you may first convert the datatable to a collection using the AsEnumerable() method and then apply your group by

Dim itms = dt.AsEnumerable().[Select](Function(x) New With {
    Key .City = x.Field(Of String)("City"),
    Key .State = x.Field(Of String)("State"),
    Key .Level = x.Field(Of String)("Level")
})



Dim uniqueStatesgroupedStates = itms.GroupBy(Function(s) s.State, Function(p) p,
                                                                Function(k, v) New With {
    Key .State = k,
    Key .Item = v
})

' IF you want group records based in State

For Each r In uniqueStatesgroupedStates
    Console.WriteLine(r.State)
Next

' IF you want only Unique states from your data table
Dim uniqueStates = itms.[Select](Function(s) s.State).Distinct().ToList()

For Each r In uniqueStates
        Console.WriteLine(r)
Next

Your expected output looks like you want only the unique State names, In that case you simply need to do a Select on State property and call Distinct() on that.

Upvotes: 2

Vivek S.
Vivek S.

Reputation: 21905

I guess you need distinct instead of a group by, If I understood your question correctly following method will work for you.

Dim resultdt As DataTable = dt.DefaultView.ToTable(True, "state")

For Each row In newdt.Rows
    Console.WriteLine(row(0).ToString)
Next

DataView.ToTable Method (): Creates and returns a new DataTable based on rows in an existing DataView

Upvotes: 1

Related Questions