[Solved] Linq query to group payment receipts for a person over a period

by


Based on the view you want to display, your models are incorrect and they need to be

public class MemberPaymentVM
{
    public string MemberName{ get; set; }
    public string TransactId { get; set; }
    [DisplayFormat(DataFormatString = "{0:d}")]
    public DateTime PayDate { get; set; }
    public IEnumerable<CategoryAmountsVM> Payments { get; set; }
    [DisplayFormat(DataFormatString = "{0:C}")]
    public decimal TotalAmount { get; set; }
}
public class CategoryAmountsVM
{
    public string Category { get; set; }
    [DisplayFormat(DataFormatString = "{0:C}")]
    public decimal Amount { get; set; }
}

Then you need to remove the Category from the grouping since you want to display each category for the group defined by Member, Transaction and Date.

The code in your controller method to generate the model needs to be

// Use .ToList() at the end so you do not load all records into memory
var filtered = db.Payments.Where(x => x.PayDate >= startdate && x.PayDate <= enddate && x.POK && x.MemberId==MemberId).ToList();

var model = filtered.GroupBy(x => new { member = x.MemberId, txn = x.TransactId, pd = x.PayDate })
    .Select(x => new MemberPaymentVM()
    {
        MemberName = x.First().Member.MemberName,
        TransactId = x.Key.txn,
        PayDate = x.Key.pd,
        TotalAmount = x.Sum(y => y.Amount),
        Payments = x.Select(y => new CategoryAmountsVM()
        {
            CategoryId = y.Category.CategoryName,
            Amount = y.Amount
        })
    });
return View(model);

Then in your view

@foreach(var item in Model)
{
    <div>
        <span>@item.MemberName</span>
        <span>@item.TransactId</span>
        <span>@Html.DisplayFor(m => item.PayDate)</span>
    </div>
    foreach(var payment in item.Payments)
    {
        <div>
            <span>@payment.Category</span>
            <span>@Html.DisplayFor(m => payment.Amount)</span>
        </div>
    }
    <div>
        <span>Total</span>
        <span>@Html.DisplayFor(m => item.TotalAmount)</span>
    <div>
}

4

solved Linq query to group payment receipts for a person over a period