vb.net - Use LINQ to Group data from data table

Question

I have a linq question. I want to use LINQ to group data from a DataTable (columns: RUN_NAME,H_YYYY,H_MM,H_MON).

The data looks like this:

RUN_NAME	H_YYYY	H_MM	H_MON
2019-1	2019	1	Jan
2019-1	2019	2	Feb
2019-1	2019	3	Mar
2019-1	2019	4	Apr
2019-2	2019	5	May
2019-2	2019	6	Jun
2019-2	2019	7	Jul
2019-2	2019	8	Aug
2019-3	2019	9	Sep
2019-3	2019	10	Oct
2019-3	2019	11	Nov
2019-3	2019	12	Dec

question from:https://stackoverflow.com/questions/65545492/use-linq-to-group-data-from-data-table

Answer 1

So my take on it is your "i need something like this" is a representation of the objects you want; 3 CalendarMonth each with a populated Month_Scope list, which are MonthScopes (4 of them)

First, I'd really like to tweak your classes a bit, for one - to remove the dodgy property names and for two - to add a couple of constructors that will construct the class from a datarow. You could structure this some other way if you like (e.g. shared FromDataRow method or factory), or you could merge this logic back into your LINQ statement if you want.. I did it as constructors because I feel it keeps it cleaner and on-point to have it that way:

Public Class CalendarMonth
    Public Property RunName As String
    Public Property MonthScopes As New List(Of MonthScope)

    Public Sub New(rn As String, ms As IEnumerable(Of DataRow))
        RunName = rn
        MonthScopes = ms.Select(Function(msro) New MonthScope(msro)).ToList()
    End Sub
End Class

Public Class MonthScope
    Public Property Yyyy As Integer?
    Public Property MM As Integer?
    Public Property Mon As String

    Public Sub New(ms As DataRow)
        If Not ms.IsNull("h_yyyy") Then Yyyy = CInt(ms("h_yyyy"))
        If Not ms.IsNull("h_mm") Then MM = CInt(ms("h_mm"))
        If Not ms.IsNull("h_mon") Then Mon = CStr(ms("h_mon"))
    End Sub
End Class

Then you have a LINQ statement of, which is a lot nicer:

    Dim calendarMonthList = dt.AsEnumerable() _
        .GroupBy(Function(r) CStr(r("run_name"))) _
        .Select(Function(g) New CalendarMonth(g.Key, g)) _
        .ToList()