How to use Grouping in LINQ?

Question

I just wish to make my question clear. This is my database.

Title      Amount       Tags
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.

The code I use right now,

var ReportingData = ReportListQuery.Where(Item => Item.Category == "expense")
                                   .GroupBy(x => new { x.Title, x.TagsReport })
                                   .Select(y => new
                                         {
                                             Percentage = Math.Round((y.Sum(x => x.Amount) / MonthExpense) * 100),
                                             ExpenseTitle = y.First().Title,
                                             ExpenseCalculation = y.Sum(x => x.Amount)
                                          });

And here's the output for my code:

Output:

Percentage     Title      Amount  
53%            Food       19
57%            Family     17

Thanks in advance. Please help me. :-(

Answer 1

As I understand your question, you don't know how to get the results per tag, right?

First of all, I strongly recommend to use meaningful names in complex LINQ queries instead of x, y, etc. Also it seems to me that you group by TagsReport for which I don't see a reason. And finally, instead of y.First().Title you can just use the group key. After applying these recommendations, your simplified query looks like this:

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)
    });

Now, to add the results per tag, you could generate another property on your anonymous type containing the list of tag amounts:

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.Tags.Select(tag => new { Tag = tag, Amount = amount})
            .GroupBy(tagAmount => tagAmount.Tag)
            .Select(tagAmountGroup => new 
            { 
                Tag = tagAmountGroup.Key, 
                TotalAmount = tagAmountGroup.Sum(tagAmount => tagAmount.Amount)
            })
    });