LINQ DataTables

Question

I have a DataTable with over 100 columns and over 280 rows that I need to group by distinct UserID, and then process each row individually. The UserIDs are unique, however, there can be multiple rows for each UserID (each UserID can have from 1 to 20 rows associated with it). All the columns, including the UserID, are String values.

I have used code like this:

Dim src = From row In sampleTable.AsEnumerable()

to return an EnumerableRowCollection(Of DataRow), but when I try to group by the UserID column of the DataTable I get an error.

I need to group the rows in the DataTable by disinct UserID and process each row. I also need to keep track of every 10 distinct UserIDs I process in order to do subprocessing requirements.

How can I group a DataTable by distinct UserID and have access to all the rows that have duplicate UserIDs?

After the replies I received thus far, I tried this:

Dim src = loanTable.AsEnumerable().ToLookup(x >= x.bSSN, x >= x)

and got an error on ToLookup(). "Overload resolution failed because no accessible 'ToLookup' can be called with these arguments (Type parameter 'TKey' cannot be inferred). In addition, the 'x' in ToLookup(x => x.UserID, x => x) returns an error that 'x' is not declared.

can you please provide your current attempt and also append the full error message. — Ousmane D.
– Ousmane D., Commented Sep 6, 2017 at 11:50

Tim Schmelter · Accepted Answer · 2017-09-06 14:43:54Z

2

I don't know your problems with GroupBy, but this works:

Dim userIdGroups = sampleTable.AsEnumerable().GroupBy(Function(row) row.Field(Of Int32)("UserId"))

Now every group contains all rows with that UserId.

Another option is to use a lookup(similar as a dictionary but you can access non available keys and you get an empty sequence of rows back instead of an exception):

Dim userIdLookup = sampleTable.AsEnumerable().ToLookup(Function(row) row.Field(Of Int32)("UserId"))

Dim userid1Rows As IEnumerable(Of DataRow) = userIdLookup(1)

You can also use a For each to enumerate all groups of the GroupBy or Lookup.

For Each rg In userIdLookup 
    Dim userid = rg.Key
    Dim count = rg.Count()
    ' if you want to enumerate the rows:
    For Each row In rg
        ' process
    Next
Next

edited Sep 6, 2017 at 14:43

answered Sep 6, 2017 at 12:02

Tim Schmelter

462k79 gold badges719 silver badges980 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Randy Johnson Over a year ago

The For each you are referring to would go like this: For Each row In userIdGroups Next --?

Tim Schmelter Over a year ago

@RandyJohnson: The row variable in your code would have a Key property where you can access the UserId.

Randy Johnson Over a year ago

Ok, I see where I can get the key for each group as well as the count, do I use a nested For Each to process each row in each group? I have to take information from each column in each row and enter it into an IBM Mainframe Reflections session.

Tim Schmelter Over a year ago

@RandyJohnson: added

Collectives™ on Stack Overflow

LINQ DataTables

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related