LINQ - quick help needed on an aggregate groupby

[mikeylikesit33]

Need some quick help as I've suffered over this easy problem all afternoon. Yes, I could do it in SQL easily then call that FillBy or 20 other different ways but I'm trying to figure out LINQ since I never used it before.

Group by Year Month and sum all three integer columns

Simple table

Date Integer1 Integer 2 Integer 3
2015/01/01 100 200 300
2015/01/02 101 201 301
2014/01/15 121 221 321

Expected output is
201501 201 401 601
201401 121 221 321


I've actually accomplished it a few times, but it's so messy I know it's stupid because of multiple steps that just don't look right. The best I've come up with is this
Dim dateGroups = From row In MyDataTable
Let year = row.Field(Of Date)("DATE").Year
Let month = row.Field(Of Date)("DATE").Month
Let Integer1 = row.Field(Of Int64)("Integer1")
Let Integer2 = row.Field(Of Int64)("Integer2")
Let Integer3 = row.Field(Of Int64)("Integer3")
Group row By Integer1, Integer2, Integer3, YearMonth = New With {Key .year = year, Key .month = month} Into DateGroup = Group Order By YearMonth.year, YearMonth.month
Dim sumGroups = From DateGroup In dateGroups
Group By DateGroup.YearMonth
Into sumInteger1 = Sum(CInt(DateGroup.Integer1)), sumInteger2 = Sum(CInt(DateGroup.Integer2)), sumInteger3 = Sum(CInt(DateGroup.Integer3))

It works! But as I said, it seems so ugly it can't be good. I should be able to do all the work in the first dim of dateGroups OR get rid of that and construct dateGroups a better way to feed my sumGroups. That first set of code does nothing more than replace my date field with a new Year and Month field. sigh...

Also, I expected I could then bind a datagrid to sumGroups but apparently not.

Can someone help an old fool who has been away from learning the new stuff too long and is desperately trying to teach myself? I seriously could do this 20 other ways in other languages or systems but this LINQ thing is baffling me for some reason.

 

[mikeylikesit33]

Figured it out! posting for others this simple solution

As is typical, soon after I wrote out the problem... the solution became a little more apparent. A little fidgeting around and a much simpler solution was arrived at. Posting the answer for others who may have similar question.



Dim sumGroups = From DateColumn In MyDataTable
Group By DateColumn._Date.Year, DateColumn._Date.Month
Into Integer1Sum = Sum(CInt(DateColumn.Integer1)), Integer2Sum = Sum(CInt(DateColumn.Integer2)), Integer3Sum = Sum(CInt(DateColumn.Integer3))

The trick was obviously to add the first half of the code below into the second halves Group By clause to make references for summing the columns available. Okay, I think I finally am starting to get this LINQ stuff. This solution was to be expected based on just previous knowledge of SQL and other languages.


Fun times learning new languages!

Need some quick help as I've suffered over this easy problem all afternoon. Yes, I could do it in SQL easily then call that FillBy or 20 other different ways but I'm trying to figure out LINQ since I never used it before.

Group by Year Month and sum all three integer columns

Simple table

Date Integer1 Integer 2 Integer 3
2015/01/01 100 200 300
2015/01/02 101 201 301
2014/01/15 121 221 321

Expected output is
201501 201 401 601
201401 121 221 321


I've actually accomplished it a few times, but it's so messy I know it's stupid because of multiple steps that just don't look right. The best I've come up with is this
Dim dateGroups = From row In MyDataTable
Let year = row.Field(OfDate)("DATE").Year
Let month = row.Field(OfDate)("DATE").Month
Let Integer1 = row.Field(OfInt64)("Integer1")
Let Integer2 = row.Field(OfInt64)("Integer2")
Let Integer3 = row.Field(OfInt64)("Integer3")
Group row By Integer1, Integer2, Integer3, YearMonth = NewWith {Key .year = year, Key .month = month} Into DateGroup = GroupOrderBy YearMonth.year, YearMonth.month
Dim sumGroups = From DateGroup In dateGroups
GroupBy DateGroup.YearMonth
Into sumInteger1 = Sum(CInt(DateGroup.Integer1)), sumInteger2 = Sum(CInt(DateGroup.Integer2)), sumInteger3 = Sum(CInt(DateGroup.Integer3))

It works! But as I said, it seems so ugly it can't be good. I should be able to do all the work in the first dim of dateGroups OR get rid of that and construct dateGroups a better way to feed my sumGroups. That first set of code does nothing more than replace my date field with a new Year and Month field. sigh...

Also, I expected I could then bind a datagrid to sumGroups but apparently not.

Can someone help an old fool who has been away from learning the new stuff too long and is desperately trying to teach myself? I seriously could do this 20 other ways in other languages or systems but this LINQ thing is baffling me for some reason.