How to use GroupBy on a DataRow collection

2018-07-17

Question

_{Tuesday, July 17, 2018 10:35 AM}

I'm doing the following -

1. Getting a table from DB into DataTable object

2. Using the Select method of the DataTable I get a DataRow collection with two fields one is a Warehouse Key the other the number of products delivered to the Wraehouse over time Something like this -

Warehous Key Amount

102 990

102 140

103 390

102 158

104 4000

etc.

I want to get it grouped by the Warehouse Key like so -

102 .....

104 .....

103 .....

etc.

Tried to use the DataRow collection (array) GroupBy but to honest could not understand it.

Can someone write a code snippet to do that?

Please make it clear and with comments.

Thanks

Ophir.

All replies (6)

_{Tuesday, July 17, 2018 11:28 AM}

Here is a clear example (using Lambda - see example for LINQ here) where the group by is the data column name, and do a DESC order by or grade data column.

Dim dt As New DataTable With {.TableName = "MyTable"}

dt.Columns.Add(New DataColumn With
{
    .ColumnName = "Identifier",
    .DataType = GetType(Integer),
    .AutoIncrement = True,
    .AutoIncrementSeed = 1
})

dt.Columns.Add(New DataColumn With {.ColumnName = "Name", .DataType = GetType(String)})
dt.Columns.Add(New DataColumn With {.ColumnName = "Grade", .DataType = GetType(Integer)})

dt.Rows.Add(New Object() {Nothing, "Bill", 70})
dt.Rows.Add(New Object() {Nothing, "Anne", 50})
dt.Rows.Add(New Object() {Nothing, "Bill", 90})
dt.Rows.Add(New Object() {Nothing, "Bill", 40})
dt.Rows.Add(New Object() {Nothing, "Anne", 100})

Dim studentData = dt.AsEnumerable.GroupBy(
    Function(student) student.Field(Of String)("Name")).
        Select(Function(group) New With
        {
            Key .Name = group.Key,
            Key .Students = group.
                    OrderByDescending(
                        Function(x) x.Field(Of Int32)("Grade"))}).
            OrderBy(Function(group) group.
                        Students.First.Field(Of String)("Name"))


For Each group In studentData
    Console.WriteLine("Group: {0}", group.Name)
    For Each student In group.Students
        Console.WriteLine("  {0}  {1}",
                            student.Field(Of Integer)("Identifier"),
                            student.Field(Of Int32)("Grade"))
    Next student
Next group

I highly advise that you hover over elements to get an idea of the underlying types e.g.

To understand what is going on under the covers.

How a few minutes to provide a better example which is strongly typed (I could had done the list now done in a for/each in the list but that is a bit more complex)

Container for data returned from Lambda statement in the upcoming code block.

Public Class WareHouseData
    Public Property Identifier() As Integer
    Public Property AmountList() As List(Of Integer)
    Public ReadOnly Property Amounts() As String
        Get
            Return String.Join(",", AmountList.ToArray())
        End Get
    End Property

    Public Overrides Function ToString() As String
        Return $"Id: {Identifier}, Amounts: {Amounts}"
    End Function
End Class

Private Sub Button3_Click(sender As Object, e As EventArgs) Handles Button3.Click
    Dim dt As New DataTable With {.TableName = "Warehouses"}

    dt.Columns.Add(New DataColumn With
    {
        .ColumnName = "Identifier",
        .DataType = GetType(Integer),
        .AutoIncrement = True,
        .AutoIncrementSeed = 1
    })

    dt.Columns.Add(New DataColumn With {.ColumnName = "WarehouseKey", .DataType = GetType(Integer)})
    dt.Columns.Add(New DataColumn With {.ColumnName = "Amount", .DataType = GetType(Integer)})

    dt.Rows.Add(New Object() {Nothing, 102, 990})
    dt.Rows.Add(New Object() {Nothing, 102, 140})
    dt.Rows.Add(New Object() {Nothing, 103, 390})
    dt.Rows.Add(New Object() {Nothing, 102, 158})
    dt.Rows.Add(New Object() {Nothing, 104, 4000})

    Dim warehouseData = dt.AsEnumerable.GroupBy(
    Function(warehouse) warehouse.Field(Of Integer)("WarehouseKey")).
        Select(Function(group) New With
        {
            Key .Warehouse = group.Key,
            Key .WhareHouseItems = group.
                    OrderByDescending(
                        Function(x) x.Field(Of Integer)("Amount"))}).
            OrderBy(Function(group) group.
                        WhareHouseItems.First.Field(Of Integer)("WarehouseKey"))


    '
    ' Place data into a list that can now be returned from a function call
    '
    Dim wareHouseDataList As New List(Of WareHouseData)
    For Each group In warehouseData
        Dim whd As New WareHouseData With {.Identifier = group.Warehouse, .AmountList = New List(Of Integer)}
        For Each item In group.WhareHouseItems
            whd.AmountList.Add(item.Field(Of Integer)("Amount"))
        Next item
        wareHouseDataList.Add(whd)
    Next group

    '
    ' Show the information
    '
    wareHouseDataList.ForEach(Sub(data) Console.WriteLine(data.ToString()))

End Sub

Results

Id: 102, Amounts: 990,158,140
Id: 103, Amounts: 390
Id: 104, Amounts: 4000

Need to sum amounts for a specific item?

Dim sumFor102 = wareHouseDataList.
        FirstOrDefault(Function(data) data.Identifier = 102).AmountList.
        Sum()

Console.WriteLine(sumFor102)

In the above case we get 1288.

Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
VB Forums - moderator

_{Tuesday, July 17, 2018 3:31 PM}

By using LINQ Group By operator, you can group your amount details by using warehouse key. Please check following code to group details based on your requirement using group by operator in linq.

Module Module1

Sub Main()

Dim objStudent As New List(Of Student)() From {

New Student() With {.Name = "Suresh Dasari", .Gender = "Male", .Location = "Chennai"},

New Student() With {.Name = "Rohini Alavala", .Gender = "Female", .Location = "Chennai"},

New Student() With {.Name = "Praveen Alavala", .Gender = "Male", .Location = "Bangalore"},

New Student() With {.Name = "Sateesh Alavala", .Gender = "Male", .Location = "Vizag"},

New Student() With {.Name = "Madhav Sai", .Gender = "Male", .Location = "Nagpur"}

}

Dim objVals = From std In objStudent Group By std.Location Into Group

For Each sitem In objVals

Console.WriteLine(sitem.Location)

Console.WriteLine()

For Each stud In sitem.Group

Console.WriteLine(stud.Name + vbTab + stud.Location)

Next

Console.WriteLine()

Next

Console.ReadLine()

EndSub

 

Class Student

Public Property Name() As String

Get

Return m_Name

End Get

Set(ByVal value As String)

m_Name = value

End Set

End Property

Private m_Name As String

Public Property Gender() As String

Get

Return m_Gender

End Get

Set(ByVal value As String)

m_Gender = value

End Set

End Property

Private m_Gender As String

Public Property Location() As String

Get

Return m_Location

End Get

Set(ByVal value As String)

m_Location = value

End Set

End Property

Private m_Location As String

End Class

End Module

To know more about group by operator in LINQ check following url.

https://www.tutlane.com/tutorial/linq/linq-groupby-method

_{Tuesday, July 17, 2018 4:07 PM}

As you instructed updated a vb.net code instead of c#.

_{Tuesday, July 17, 2018 4:46 PM}

As you instructed updated a vb.net code instead of c#.

Last thing, would be nice for the code to be formatted :-)

Module Module1
    Sub Main()
        Dim objStudent As New List(Of Student)() From {
            New Student() With {.Name = "Suresh Dasari", .Gender = "Male", .Location = "Chennai"},
            New Student() With {.Name = "Rohini Alavala", .Gender = "Female", .Location = "Chennai"},
            New Student() With {.Name = "Praveen Alavala", .Gender = "Male", .Location = "Bangalore"},
            New Student() With {.Name = "Sateesh Alavala", .Gender = "Male", .Location = "Vizag"},
            New Student() With {.Name = "Madhav Sai", .Gender = "Male", .Location = "Nagpur"}
        }

        Dim objVals = From std In objStudent Group By std.Location Into Group

        For Each sitem In objVals
            Console.WriteLine(sitem.Location)
            Console.WriteLine()
            For Each stud In sitem.Group
                Console.WriteLine(stud.Name + vbTab + stud.Location)
            Next
            Console.WriteLine()
        Next
        Console.ReadLine()
    End Sub
    Class Student
        Public Property Name() As String
            Get
                Return m_Name
            End Get
            Set(ByVal value As String)
                m_Name = value
            End Set
        End Property
        Private m_Name As String
        Public Property Gender() As String
            Get
                Return m_Gender
            End Get
            Set(ByVal value As String)
                m_Gender = value
            End Set
        End Property
        Private m_Gender As String
        Public Property Location() As String
            Get
                Return m_Location
            End Get
            Set(ByVal value As String)
                m_Location = value
            End Set
        End Property
        Private m_Location As String
    End Class
End Module

_{Tuesday, July 17, 2018 5:44 PM}

Thanks for the reply, however it does not make use of the DataRow collection returned by the Table.Select, GroupBy function, or maybe I do not fully understand your example.

Regards

_{Tuesday, July 17, 2018 6:22 PM}

Hi

Thanks for the reply, however it does not make use of the DataRow collection returned by the Table.Select, GroupBy function, or maybe I do not fully understand your example.

Regards

Well that is the wrong approach, you need to do the select after the group by, extremely simple example beings I'm at work.

Dim groupResults = GetMockedData().
    AsEnumerable().
    GroupBy(Function(row) row.Field(Of String)("URL_Link")).
    Select(Function(grp) grp.First())

שתף באמצעות

How to use GroupBy on a DataRow collection

Question

All replies (6)

משאבים נוספים