VB.NET: How to implement Group By in DataSet using LINQ

In this post I will show you the power of LINQ. At first I never try LINQ even when I developing project using Visual Studio 2008. But since I have no way to find a solution to one of my project, I start to explore LINQ which is give me a thought of how powerful and simple it become when doing a coding using LINQ.

So this is the case, I’m having a dataset with table SalesOrderDetail:

And the data inside is :

The question is, How to sum OrderQty and LineTotal based on SalesOrderID?

Some people might said, why don’t you just group by in store procedure or SQL Query when you calling the database. Of course than can be done easily, but what if you get this data from webservices or from csv / textfile which is make imposible to query it. This is why the LINQ comes in hand to solve this problem (Correct me if im wrong )

How to use LINQ for this case:

First:

Imports System.Linq

Second:

Public Class Form2

    Private Sub Form2_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
        Dim dt As New DataSet1TableAdapters.SalesOrderDetailTableAdapter
        Dim result = From c In dt.GetData() Group c By c.SalesOrderID Into OrderQty = Sum(c.OrderQty), LineTotal = Sum(c.LineTotal) Select SalesOrderID, OrderQty, LineTotal
        Me.DataGridView1.DataSource = result.ToList()
    End Sub

End Class

Result:

So as you can see, at this line:

Dim result = From c In dt.GetData() Group c By c.SalesOrderID Into OrderQty = Sum(c.OrderQty), LineTotal = Sum(c.LineTotal) Select SalesOrderID, OrderQty, LineTotal

Result is the variable of the LINQ output, c is the variable of dt.GetData(), then you gtoup c by salesOrderID into new variable called OrderQty and so on. At the end you select the new variable that already sum / group by.

In order to shows the output to the datagridview, Im using result.ToList(). But there is a problem with this method, the sequnce of the column is based on alphabetical order. Even if I already set in datagridview poperties, it still back to the alphabetical sequence. I hope someone can answer this question.

This example is done in Visual Studio 2008.

If you want to see more detail about LINQ example, this is the good site:

http://msdn.microsoft.com/en-us/vcsharp/aa336746.aspx

Good luck.