GroupBy in LINQ?

Question

I'm working on the database application which shows clear report of the transaction by its tags.

This is my database.

Title      Amount       TagsReport (string[] array)
Food       5            "Hotel","Friends"
Food       6            "Hotel"
Family     8            "Hotel","Mobile"
Family     9            "Electricity"
Food       8            "Party"

I wish to generate the report like below:

Desired Output:

Percentage     Title             Amount
53%            Food              19
                  Hotel             11
                  Friends           5
                  Party             8

57%            Family            17
                 Hotel             8
                 Mobile            8
                 Electricity       9

I don't have enough knowledge on LINQ. So I face lot of trouble in this finding a perfect solution.

However I find this code to use,

var ReportingData = ReportListQuery
    .Where(Item => Item.Category == "expense")
    .GroupBy(item => item.Title)
    .Select(itemGroup => new
    {
        Percentage = Math.Round((itemGroup.Sum(item => item.Amount) / MonthExpense) * 100),
        ExpenseTitle = itemGroup.Key,
        ExpenseCalculation = itemGroup.Sum(item => item.Amount),
        TotalTagAmounts = itemGroup
        .SelectMany(item => item.TagsReport.Select(tag => new { 
            Tag = tag, 
            Amount = item.Amount 
        })
        .GroupBy(tagAmount => tagAmount.Tag)
        .Select(tagAmountGroup => new
        {
            Tag = tagAmountGroup.Key,
            TotalAmount = tagAmountGroup.Sum(tagAmount => tagAmount.Amount)
        }))
    });

And the output is like;

Percentage     Title                  Amount  
53%            Food                   19
                 Hotel                  5
                 Friends                5
                 Hotel                  6 //Hotel should be grouped here and should with the value 11 :-(
                 Party                  8

57%            Family                 17
                 Hotel                  8
                 Mobile                 8               
                 Electricity            9

But my desired output is:

Percentage     Title             Amount
53%            Food              19
                  Hotel             11
                  Friends           5
                  Party             8

57%            Family            17
                 Hotel             8
                 Mobile            8
                 Electricity       9

As you see, I made everything. But not the 'hotel' grouped in the reports. What am I doing wrong? Please advise!

Answer 1

I concur with Chris Cummings' observation regarding the misapplication of the grouping. Because you don't flatten (i.e. call SelectMany() on the TagsReport field until after you only have a single item, grouping the result winds up with just one group for each flattened sequence.

Based on your stated desired output, it seems that you really just have one misplaced closing parenthesis. You should write this instead:

var ReportingData = ReportListQuery
    .Where(Item => Item.Category == "expense")
    .GroupBy(item => item.Title)
    .Select(itemGroup => new
    {
        Percentage = Math.Round((itemGroup.Sum(item => item.Amount) / MonthExpense) * 100),
        ExpenseTitle = itemGroup.Key,
        ExpenseCalculation = itemGroup.Sum(item => item.Amount),
        TotalTagAmounts = itemGroup
        .SelectMany(item => item.TagsReport.Select(tag => new
        {
            Tag = tag,
            Amount = item.Amount
        })) // add parenthsis here
        .GroupBy(tagAmount => tagAmount.Tag)
        .Select(tagAmountGroup => new
        {
            Tag = tagAmountGroup.Key,
            TotalAmount = tagAmountGroup.Sum(tagAmount => tagAmount.Amount)
        }) // remove parenthesis here
    });

In other words: you want to group the whole flattened sequence of items within each "grouped-by-title" group, rather than flattening each individual item within each group. I simply removed the second ) on the next-to-the-last line, and added one just before the second GroupBy() call.

This produces exactly the output you describe. It's still odd (the sum of the individual tag amounts exceeds the total expense for each title group), but at least it's what you specifically asked for. :) (Well, not counting the percentages…given the example data, there's no way to have a single MonthExpense value for which the percentages add up, so I just didn't bother to try to make that number come out right).