AspdotnetCodes.com
Search
Articles
Books
Resources
Asp.Net News
Downloads
Free Tech Magazines
Asp.Net Web Hosting
Archives
Softwares
Newsletter
Suggest Us
Link to Us
Feeds Subscription
Articles
Questions & Answers
Tips & Tricks
 How to Group Multiple DataTable Columns through LINQ to DataSet (VB.NET)?
Posted by Moderator1 on  9/17/2015 9:47:42 AM 2944 Views
One day, I want to group a DataTable with multiple columns. As I don’t want to do this another SQL Statement, I want to try with the traditional way in LINQ to DataSet. But the result is not what I expect. Even though all the records are of same values, the columns are not grouped. So, I provide the correct syntax of grouping the DataTable through LINQ to DataSet concept below in VB.NET.

Dim dt As New DataTable()
dt.Columns.Add("ProductName")
dt.Columns.Add("ProductCategory")
dt.Columns.Add("ProductSubCategory")
dt.Columns.Add("ProductPrice", System.Type.[GetType]("System.Decimal"))

Dim dr1 As DataRow = dt.NewRow()
dr1("ProductName") = "Skirt Red"
dr1("ProductCategory") = "Female"
dr1("ProductSubCategory") = "Kids"
dr1("ProductPrice") = 19
dt.Rows.Add(dr1)
Dim dr2 As DataRow = dt.NewRow()
dr2("ProductName") = "Jeans Blue"
dr2("ProductCategory") = "Male"
dr2("ProductSubCategory") = "Kids"
dr2("ProductPrice") = 25
dt.Rows.Add(dr2)
Dim dr3 As DataRow = dt.NewRow()
dr3("ProductName") = "Jeans Black"
dr3("ProductCategory") = "Male"
dr3("ProductSubCategory") = "Adult"
dr3("ProductPrice") = 25
dt.Rows.Add(dr3)
Dim dr4 As DataRow = dt.NewRow()
dr4("ProductName") = "Jeans White"
dr4("ProductCategory") = "Male"
dr4("ProductSubCategory") = "Adult"
dr4("ProductPrice") = 25
dt.Rows.Add(dr4)

Dim newGroup = (From row In dt.AsEnumerable() Group row By Grp = New With { _
Key .ProductCategory = row.Field(Of String)("ProductCategory"), _
Key .ProductSubCategory = row.Field(Of String)("ProductSubCategory")} _
Into G = Group
Select New With { _
.ProductSubCategory = Grp.ProductSubCategory, _
.ProductCategory = Grp.ProductCategory, _
.TotalPrice = G.Sum(Function(r) r.Field(Of [Decimal])("ProductPrice")) _
})

For Each item In newGroup
Response.Write(Convert.ToString(item.ProductCategory) & " | " & Convert.ToString(item.ProductSubCategory) & " | " & item.TotalPrice.ToString() & "<br/>")
Next

Post New Tips/Tricks View all Tips/Tricks

Featured Resources:
Backup and Recovery Best Practices for Microsoft SQL Server 2005
To help you choose from among the available configuration options and backup and recovery procedures, HP has conducted extensive laboratory tests to determine best practices.
The PHP Anthology: 101 Essential Tips, Tricks & Hacks, 2nd Edition - Free 207 Page Preview!
A compilation of the best solutions provided to common PHP questions.
100% Free Subscription until Stock lost.View complete list.
Sponsored by