CODEWITHSUNDEEP
vb.netlinq
uqji
uqji

Reputation: 195

A simple linq query

Here is the situation: I have huge amount of data stored in 'mysamples' as the below data structure. I need to do some query and reporting job on the data and I am new to Linq. To start with, I need to write a Linq script to extract the monthly year-to-date report for all records. It is to sum up the 'value' of all samplepoints (in the current year, categorized by month) of all samples in 'mysamples'. I can easily do it in vb.net code, but I realized that Linq is the right tool for this kinda stuff. Can anybody give me something to start with. Thanks. 

Public Structure sample
    Public Property id As String
    Public datapoints As List(Of sampledatapoint)
End Structure

Public Structure sampledatapoint
    Public time As DateTime
    Public value As Decimal
End Structure

Public mysamples As New List(Of sample)

Upvotes: 0

Views: 68

Answers (1)

Alex B.
Alex B.

Reputation: 2167

    Dim sampleQ1 as New sample() With {.id ="2016Q1", .datapoints = New List(Of sampledatapoint)()}
    sampleQ1.datapoints.Add(New sampledatapoint() With {.time = New DateTime(2016, 1, 1), .value = 1000})
    sampleQ1.datapoints.Add(New sampledatapoint() With {.time = New DateTime(2016, 1, 10), .value = 2000})
    sampleQ1.datapoints.Add(New sampledatapoint() With {.time = New DateTime(2016, 2, 1), .value = 500})
    sampleQ1.datapoints.Add(New sampledatapoint() With {.time = New DateTime(2016, 3, 7), .value = 750})

    Dim sampleQ2 as New sample() With {.id ="2016Q2", .datapoints = New List(Of sampledatapoint)()}
    sampleQ2.datapoints.Add(New sampledatapoint() With {.time = New DateTime(2016, 4, 4), .value = 800})
    sampleQ2.datapoints.Add(New sampledatapoint() With {.time = New DateTime(2016, 4, 5), .value = 150})

    Dim sampleQ4 as New sample() With {.id ="2015Q4", .datapoints = New List(Of sampledatapoint)()}
    sampleQ4.datapoints.Add(New sampledatapoint() With {.time = New DateTime(2015, 10, 20), .value = 666})

    Dim mysamples As New List(Of sample)  
    mysamples.Add((sampleQ1))
    mysamples.Add((sampleQ2))
    mysamples.Add((sampleQ4))

    Dim q = From sdp In mysamples.SelectMany(Function(x) x.datapoints).Where(Function(x) x.time.Year = DateTime.Now.Year)
            Group By grp = sdp.time.Month Into Group
            Select New With {.Month = grp, .Sum = Group.Sum(Function(x) x.value)}

    For Each monthlySum In q
        Console.WriteLine("Month: {0}, Sum: {1}", monthlySum.Month, monthlySum.Sum)
    Next

    'Month: 1, Sum: 3000
    'Month: 2, Sum: 500
    'Month: 3, Sum: 750
    'Month: 4, Sum: 950

SelectMany does the trick. It flattens all sampledatapoints of all samples into one list. The Group By remains the same.

Additionally: is there any reason why you use struct instead of class? I would suggest to use a class at least for sample since it neither represents a single value nor does it contain value types.

Upvotes: 1

Related Questions