LINQ GroupBy on object with several levels

Question

I've got some classes declared like this:

public class UserSpend
{
    public string UserName{ get; set; }
    public MonthSpend[] Spend { get; set; }

}

public class MonthSpend
{
    public DateTime Month { get; set; }
    public SpendDetail[] Detail { get; set; }

}

public class SpendDetail
{
    public string Description { get; set; }
    public decimal Amount { get; set; }
}

which as you can see has several levels. I have declared a variable like this:

UserSpend[] allSpend;

which I populate so that I have several UserSpend's which each have several MonthSpend's which each have several SpendDetail's. So the data looks something like this:

allSpend =
UserSpend (UserName = "Bob", Spend = [
        MonthSpend (Month = "01/09/12", Detail = [
            SpendDetail (Description = "Local", Amount = "12.00"),
            SpendDetail (Description = "National", Amount = "7.00") ]
        MonthSpend (Month = "01/10/12", Detail = [
            SpendDetail (Description = "Local", Amount = "8.00"),
            SpendDetail (Description = "Mobile", Amount = "3.00"),
            SpendDetail (Description = "National", Amount = "15.00") ]
        MonthSpend (Month = "01/11/12", Detail = [
            SpendDetail (Description = "Local", Amount = "16.00"),
            SpendDetail (Description = "National", Amount = "5.00") ] ]
UserSpend (UserName = "Jack", Spend = [
        MonthSpend (Month = "01/09/12", Detail = [
            SpendDetail (Description = "Local", Amount = "1.00"),
            SpendDetail (Description = "National", Amount = "4.00"),
            SpendDetail (Description = "International", Amount = "17.00") ]
        MonthSpend (Month = "01/10/12", Detail = [
            SpendDetail (Description = "Local", Amount = "2.00"),
            SpendDetail (Description = "Mobile", Amount = "7.00"),
            SpendDetail (Description = "International", Amount = "5.00") ]
        MonthSpend (Month = "01/11/12", Detail = [
            SpendDetail (Description = "Local", Amount = "6.00"),
            SpendDetail (Description = "National", Amount = "2.00") ] ]

What I'd like to do is populate this variable:

public class UserDataPoint
{
    public string User { get; set; }
    public string Category { get; set; }
    public int Spend { get; set; }
}

UserDataPoint[] userData;

so that userData contains something like this:

userData = UserDataPoint (
[User = "Bob", Category = "Local", Spend = 36.00]
[User = "Bob", Category = "Mobile", Spend = 3.00]
[User = "Bob", Category = "National", Spend = 27.00]
[User = "Jack", Category = "Local", Spend = 9.00]
[User = "Jack", Category = "Mobile", Spend = 6.00]
[User = "Jack", Category = "National", Spend = 7.00]
[User = "Jack", Category = "International", Spend = 22.00]

So for each UserName I'd like to group by Description and sum the Amount. I'm trying to learn LINQ and have been trying to use GroupBy to achieve this, but I'm not getting anywhere. Please could someone provide an example of how to achieve this. Many thanks, Colin.

score 3 · Accepted Answer · edited May 23 '17 at 12:21

With syntax query in LINQ:

userData = from spend in allSpend
           from monthSpend in spend.Spend
           from spendDetail in monthSpend.Detail
           group spendDetail by new {spend, spendDetail.Description} into g
           select new UserDataPoint
           {
               User = g.Key.spend.UserName,
               Category = g.Key.Description,
               Spend = g.Sum(t => t.Amount)
           };

The difficulty here is to group by multiple columns (user name and category). It is done using anonymous type in the group ... by {spend, spendDetail.Description}. See Stackoverflow

Update

Here is a better version (no need for a complex GroupBy):

userData = from spend in allSpend
           from userDataPoint in
               (from monthSpend in spend.Spend
               from spendDetail in monthSpend.Detail
               group spendDetail by spendDetail.Description into g
               select new UserDataPoint
               {
                   User = spend.UserName,
                   Category = g.Key,
                   Spend = g.Sum(t => t.Amount)
               })
           select userDataPoint;

I mean 1000 users with 20 month with 50 spent details. Are you sure, that the cross join is the best solution here? — YD1m, Jul 24 '13 at 12:22

LINQ GroupBy on object with several levels

1 Answers1

Update

Linked