Categories
Uncategorized

Linq Grouping of a Datatable

Linq is fantastic technology from Microsoft which help us programmers with transforming data from one form to another and combining different sources far simpler than with ADO.Net. Typically it is used against database but it can be used on multiple data source types. It can even do wonderful things to simplify data traversal and data binding in ASP.net web forms.

A recent solution I needed was with  a very simple hierarchy.  Only two levels deep, it didn’t need recursion. The usual way to accomplish this is will a loop and watching for changes in the parent value. In pseudo-code this would be like.


itemslist = read items from database

parent cat = nothing

loop itemslist i

new item = itemslist [i]

if new item.category not equal parent cat

tree. new item = new item

parent cat = new item.category

else

tree.add item ( new item )

next

Phew that was harder than I thought it would be.

But what if this tree could be represented as a nest set of lists? Wow! So how would we do this in linq, and using datatables no less.

So here is what I was able to come up with


Dim groupLocations = (From locs In dtLocations.AsDataView Group locs By LocationGroup = locs("LocationGroup") Into locGroup = Group
 Select New  With {
 .LocationGroup = locGroup(0)("LocationGroup"),
 .Locations = (From locs In locGroup
 Select New  With {
 .LocationName = locs("LocationName"),
 .LocationNumber = locs("LocationNumber"),
 .LocationID = locs("LocationID")}).ToList()
 }
 ).ToList()

Datatables are useable in linq, but require a little bit of funny syntax and you are dealing with DataRowView objects. So you must use the ‘asdataview’ in this case and the datarowview(“[item]”) method of accessing the columns.

So what you have from this is a list item for each group and contained within each a list of the children items.

So if you were populating a tree from this list it could be done like this


For Each grp  In groupLocations
 Dim oGroupParent As DynaTreeNode
 oGroupParent = New DynaTreeNode(grp.LocationGroup, "G" & grp.LocationGroup)
 oGroupParent.CssClass = "groupnode"
 lNodes.Add(oGroupParent)
 For Each Loc In grp.Locations
 Dim oLoc As DynaTreeNode
 oLoc = New DynaTreeNode(Loc.LocationName, Loc.LocationID)
 oLoc.CssClass = "locationnode"
 oGroupParent.children.Add(oLoc)
 Next
 Next

Once again linq saves the day. And it is possible to use similar techniques to filling data for scenarios like nested repeaters. I might just blog on that next time.

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.