Group by a column then Add each group to datatable then into a dataset

Question

what would be the best approach to such thing?

so here is my Datatable

╔═══════════════╦═══════════════╦═══════════════╗
║ Product Name  ║ Product Price ║ Product Group ║
╠═══════════════╬═══════════════╬═══════════════╣
║ Skirt Red     ║            99 ║             1 ║
║ Jeans Blue    ║            49 ║             2 ║
║ Jeans Black   ║            49 ║             2 ║
║ Skirt Blue    ║            99 ║             1 ║
║ T-shirt White ║            20 ║             3 ║
║ T-shirt Green ║            20 ║             3 ║
║ Jeans Grey    ║            49 ║             2 ║
╚═══════════════╩═══════════════╩═══════════════╝

i will group this datatable by the product group column using LINQ to produce the following groups

Group #1
    ╔═══════════════╦═══════════════╦
    ║ Product Name  ║ Product Price ║ 
    ╠═══════════════╬═══════════════╬
    ║ Skirt Red     ║            99 ║             
    ║ Skirt Blue    ║            99 ║             
    ╚═══════════════╩═══════════════╩

Group #2
        ╔═══════════════╦═══════════════╦
        ║ Product Name  ║ Product Price ║ 
        ╠═══════════════╬═══════════════╬
        ║ Jeans Blue    ║            49 ║             
        ║ Jeans Black   ║            49 ║ 
        ║ Jeans Grey    ║            49 ║
        ╚═══════════════╩═══════════════╩
Group #3
        ╔═══════════════╦═══════════════╦
        ║ Product Name  ║ Product Price ║ 
        ╠═══════════════╬═══════════════╬
        ║ T-Shirt White ║            20 ║             
        ║ T-Shirt Green ║            20 ║ 
        ╚═══════════════╩═══════════════╩

now the questions are

1. how do i group by the Product group column using LINQ (Done)
2. how do i remove the Product group column from the resulted group?
3. how do i add each group to a seperate datatable and then add all tables to single dataset?(Done)
4. suppose there were columns that i dont want to show in the resulted group, how can i hide them?

here is what I've tried so far

    Dim ds As New DataSet
    Dim query = From r In bookedorders Group By key = r.Field(Of Integer)("productgroup") Into Group
    For Each grp In query
        Dim x As New DataTable
        x = grp.Group.CopyToDataTable()
        ds.Tables.Add(x)
    Next

now this is working except i am not sure how to select specific columns, like i don't want to show all columns in the resulted datatables

Answer 1

1.how do i group by the Product group column using LINQ

It sounds like what you are asking for isn't technically called a group in LINQ. Grouping in linq implies that you are taking the values from one column and combining them in some way(SUM, Average) and displaying one record per some unique identifier. In your example if you wanted to show the average price per group then that would require a group but from your explanation it looks like you just need 3 different select statements that would look like:

From r in products where r.groupID = YourControlVariable select r.ProductName, r.ProductPrice

Where YourControlVariable would be each group ID. This LINQ would give you the three tables that you outlined and from there you could call the CopyToDataTable function on what the LINQ returned and set a temporary datatable equal to the output of that function.

2.how do i remove the Product group column from the resulted group?

Refer to the linq from point 1, by listing only the columns you want to select you will essentially be ignoring any of the columns that aren't listed.

3.how do i add each group to a seperate datatable and then add all tables to single dataset?

This would be fairly simple to accomplish in a loop if you know the groupID's you will be working with. you can create a temporary datatable inside a loop and set it to contain the results of the linq from point 1. once you have the results you can then name and add the table to a dataset and once the loop is finished your temporary datatables will be gone but you will be able to reference them by name in the dataset itself.

4.suppose there were columns that i dont want to show in the resulted group, how can i hide them?

Refer to point 1/2.

Edit:

As you've described the problem I think using a group by in the LINQ makes it unnecessarily complicated, in that you have to deal with anonymous types if you want to pull out individual properties. Here is a short snippet that gets all the distinct group ids then uses LINQ to pull out all products of each type, addsd them to a table and then adds the named table to the dataset. You can remove the name portion or change it to be whatever you want it doesn't really matter.

Dim ds As New DataSet
Dim groupIDs = From r in bookedorders Select r.Item("productgroup") Distinct
for each r in groupIDs
    Dim query = From t in bookedorders Where t.Item("productgroup") = r select t.Item("ProductName"), t.Item("ProductPrice")
    If query IsNot Nothing AndAlso query.Any Then
        Dim tempDT as new DataTable
        tempDT.Merge(query.CopyToDataTable)
        tempDt.Name = "ProductID" & r
        ds.Tables.Add(tempDT)
    End IF
Next

I've changed your example a little bit to use the Item collection but that will only work if bookedorders is a datatable.

Edit #2:

After thinking about it for a bit, the above example will still give you a collection of anonymous types as a result. So to get around that you could change the

Dim query = From t in bookedorders Where t.Item("productgroup") = r select t.Item("ProductName"), t.Item("ProductPrice")

line to look like:

Dim query = From t in bookedorders Where t.Item("productgroup") = r select t

and then inside your if statement will look like:

Dim tempDT as new DataTable
tempDT.Merge(query.CopyToDataTable)
tempDT.Columns.Remove("GroupID")
tempDt.Name = "ProductID" & r
ds.Tables.Add(tempDT)

Again this will only work if you are LINQing through a datatable and getting an Enumerable(Of DataRow) as your result, which is preferable.

Answer 2

I'm not used to VB.NET syntax, it should be something like this (I do not guarantee 100% that code actually compiles):

Dim query = From r In bookedorders _
            Group By key = r.Field(Of Integer)("productgroup") Into Group _
            Select New With { _
              .key, _
              .Rows = From o In Group _
                      Select New With { _
                          .Name = r.Field(Of String)("productname") } }

For further checks you can see this:

http://msdn.microsoft.com/en-us/vstudio/bb737908

and in particular the GroupBy - Nested sample, where you can see how you can project inside a group.

If you work it out and find some compile error in my answer let me know what and I'll fix it.