Linq Group by 6 Categories and Person

Question

I need help to construct a linq query to display payments grouped into categories which could be filtered by Year, Month and Member Name

I have these models

Member(MemberId,MemberName)
Payment(PayId, MemberId[foreignkey], PayDate, Amount,CategoryId[foreignkey])
Category(CategoryId, CategoryName}

Looking at getting an output like below John could have made two different payments for the same category for the month of April (04) and such payments are added together (sum)

Name  Year Month   Cat1     Cat2    Cat3    Cat4   Cat5    Cat6
John  2016  04     12.30    0.00    30.60   15.20  120.00  15.40
Fred  2016  04     10.30    22.50   80.60   0.00   100.00  19.40

I have tried using joins but my real life application has 25 Payment categories and joining the same table for 25 times exceeds MYSQL permissible sub selects on the production server.

I will be happy to receive direction on the best way to achieve this

Answer 1

You have indicated in the comments that you want to filter my Month/Year so there seems no need to include those columns in the table and instead you could use a heading for the table, say "Results for April 2016".

Start by defining view models to represent want you want to display in the view

public class MemberPaymentVM // for the table rows
{
    public MemberPaymentVM(int categoryCount)
    {
        Amounts = new List<decimal>(new decimal[categoryCount]);
    }
    public string Name { get; set; }
    public List<decimal> Amounts { get; set; }
    public decimal Total { get; set; }
}
public class MonthPaymentsVM
{
    [DisplayFormat(DataFormatString = "{0:MMMM yyyy}"]
    public DateTime Date { get; set; }
    public IEnumerable<string> Categories { get; set; }
    public List<MemberPaymentVM> Payments { get; set; }
}

The following queries can be chained, but to break this into each component, first filter the data

var date = new DateTime(2016, 4, 1);
var filtered = db.Payments.Where(x => x.PayDate >= date && x.PayDate < date.AddMonths(1));

Then group the data by both Member and Category and sum the results (I'm assuming that your Payment model contains virtual properties for the relationships

var grouped = filtered.GroupBy(x => new { member = x.Member.ID, category = x.Category.ID }).Select(x => new
{
    Member = x.First().Member,
    Category = x.First().Category,
    Amount = x.Sum(y => y.Amount)
});

Then group again by Member to represent each table row

var data = grouped.GroupBy(x => x.Member.ID);

Next, get your categories

var categories = db.Categories;
var categoryCount = categories.Count();
var categoryIDs = categories.Select(x => x.CategoryId).ToList();

And the build you view models

var model = new MonthPaymentsVM()
{
    Date = date,
    Categories = categories.Select(x => x.CategoryName),
    Payments = new List<MemberPaymentVM>()
};
foreach(var group in data)
{
    MemberPaymentVM payment = new MemberPaymentVM categoryCount);
    payment.Name = group.First().Member.Name;
    foreach (var item in group)
    {
        int index = categoryIDs.IndexOf(item.Category.CategoryId);
        payment.Amounts[index] = item.Amount;
        payment.Total += item.Amount;
    }
    model.Payments.Add(payment);
}

and finally, return the model to the view

return View(model);

And in the view

@model MonthPaymentsVM
<h2>@Html.DisplayFor(m => m.Date);
<table>
    <thead>
        <tr>
            <td>Name</td>
            @foreach(var category in Model.Categories)
            {
                <td>@category</td>
            }
            <td>Total</td>
        </tr>
    </thead>
    <tbody>
        @foreach (var item in Model.Payments)
        {
            <tr>
                <td>@item.Name</td>
                @foreach (var amount in item.Amounts)
                {
                    <td>@amount</td> // @amount.ToString("c") to format it
                }
                <td>@item.Total</td>
            </tr>
        }
    </tbody>
</table>