CODEWITHSUNDEEP
.netvb.netlinqlinq-to-sqlgroup-by
GJKH
GJKH

Reputation: 1725

LINQ Group By and aggregate fields

I've always struggled with grouping in LINQ, I'd like to group based on the date part of a datetime field and aggregate other fields.

Example table layout

myDateField     myIntField1    myIntField2    myIntField3
01/02/2013      5              5              5
01/02/2013      5              5              5
02/02/2013      10             10             10
02/02/2013      10             10             10

I'd like to return a list with

myDateField     myIntField1    myIntField2    myIntField3
01/02/2013      10             10             10
02/02/2013      20             20             20

I've managed the following using msdn examples but can't really get my head around how to use it. It states I should be able to access the new BookedList group but keep getting annonymous type errors and such.

        Dim bestdays = From a In b
                        Where a.BookedOn < a.EventDayTime And a.Cancelled = False
                        Group By BookDate = a.BookedOn.Value.Date
                        Into BookedList = Group

Upvotes: 0

Views: 223

Answers (2)

Victor Zakharov
Victor Zakharov

Reputation: 26454

The syntax is Into [FIELD_NAME1] = [AGGREGATE_FUNCTION1]([VALUE1]), [FIELD_NAME2] = [AGGREGATE_FUNCTION2]([VALUE2]) .... The key will automatically be part of returned object.

So you are looking for something like this:

Class TableRow
  Public Property myDateField As Date
  Public Property myIntField1 As Integer
  Public Property myIntField2 As Integer
  Public Property myIntField3 As Integer
  Sub New(mydate As Date,
          myint1 As Integer, myint2 As Integer, myint3 As Integer)
    myDateField = mydate
    myIntField1 = myint1
    myIntField2 = myint2
    myIntField3 = myint3
  End Sub
End Class

Sub Main()
  Dim tablesRows As New List(Of TableRow)
  tablesRows.Add(New TableRow(Today, 5, 10, 15))
  tablesRows.Add(New TableRow(Today, 6, 11, 16))

  Dim v = From r In tablesRows Group By r.myDateField
          Into Total1 = Sum(r.myIntField1),
               Total2 = Sum(r.myIntField2),
               Total3 = Sum(r.myIntField3)
End Sub

Upvotes: 1

Emmanouil Chountasis
Emmanouil Chountasis

Reputation: 590

Maybe this sld do your job

Dim bestdays = (From a In b
               Where a.BookedOn < a.EventDayTime And a.Cancelled = False
               Group By BookDate = a.BookedOn.Value.Date
               Into BookedList = Group).Take(5)

Upvotes: 1

Related Questions