Linq集团按6类和个人划分

Question

我需要帮助来构造一个linq查询，以显示按年份、月份和成员名称进行过滤的按类别分组的支付。

我有这些模型

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

如果想得到像下面这样的产出，约翰可以在4月份(04)为同一类别支付两笔不同的款项，这些付款加在一起(和)。

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

我试过使用joins，但是我的实际应用程序有25个支付类别，并且加入同一个表25次，超过MYSQL允许的在生产服务器上的子选择。

我很乐意接受关于实现这一目标的最佳方法的指导。

Answer 1

您在评论中指出，您希望筛选我的月份/年，因此似乎没有必要将这些列包括在表中，而可以使用表格的标题，例如“2016年4月的结果”。

从定义要在视图中显示的视图模型开始

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

可以将以下查询链接起来，但要将其分解为每个组件，首先要筛选数据

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

然后按Member和Category对数据进行分组，并对结果进行求和(我假设Payment模型包含关系的虚拟属性

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

然后再按Member分组以表示每一行表

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

接下来，找出你的分类

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

而您所查看的构建模型

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

最后，将模型返回到视图。

return View(model);

在视野中

@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>