使用LINQ创建JSON

Question

这是我现有的表结构： 现在我需要生成以下JSON：

{
    Name: "AccCat1",
    Credit: 123,
    Debit: 123,
    AccountGroups:[
        {
            Name: "AccGrp1",
            Credit: 123,
            Debit: 123,
            Accounts: [
                {
                    Name: "Acc1",
                    Credit: 123,
                    Debit: 123,
                    AccountParticulars:[
                        {
                            Name: "AccPar1",
                            Credit: 123,
                            Debit: 123
                        },
                        {
                            Name: "AccPar2",
                            Credit: 123,
                            Debit: 123
                        }
                    ]
                }
            ]
        }
    ]
}

我需要从JournalRecord开始，逐步创建JSON输出。到目前为止，我能够做到的只有下面这些，但是可以看出来，由于我一路上都在累加借方和贷方的值，所以它们是相同的，而不是按照实际情况：

var records = db.JournalEntries.Include(je => je.JournalRecords.Select(jr => jr.Account).Select(j => j.AccountParticulars))
            .Where(je => je.Date >= from && je.Date <= to)
            .SelectMany(s => s.JournalRecords)
            .GroupBy(d => d.AccountParticular.Account.AccountGroup.AccountCategory)
            .Select(g => new
            {
                Name = g.Key.Name,
                Credit = g.Sum(c => c.Credit),
                Debit = g.Sum(d => d.Debit),
                AccountGroups = g.Key.AccountGroups.Select(ag => new
                {
                    Name = ag.Name,
                    Credit = g.Sum(c => c.Credit),
                    Debit = g.Sum(d => d.Debit),
                    Accounts = ag.Accounts.Select(ac => new
                    {
                        Name = ac.Name,
                        Credit = g.Sum(c => c.Credit),
                        Debit = g.Sum(d => d.Debit),
                        AccountParticulars = ac.AccountParticulars.Select(ap => new 
                        {
                            Name = ap.Name,
                            Credit = g.Sum(c => c.Credit),
                            Debit = g.Sum(d => d.Debit)
                        })
                    })
                })
            });

这是我的代码生成的内容：

[
  {
    "Name": "Cat1",
    "Credit": 11000, <--Total (correct)
    "Debit": 11000, <--Total (correct)
    "AccountGroups": [
      {
        "Name": "Grp1",
        "Credit": 11000, <--Total (correct)
        "Debit": 11000, <--Total (correct)
        "Accounts": [
          {
            "Name": "Acc1",
            "Credit": 11000, <--Total (correct)
            "Debit": 11000, <--Total (correct)
            "AccountParticulars": [
              {
                "Name": "AccPar1",
                "Credit": 11000, <-- Should be 500. From JournalRecord
                "Debit": 11000 <-- Should be 500. From JournalRecord
              },
              {
                "Name": "AccPar2",
                "Credit": 11000, <-- Should be 500. From JournalRecord
                "Debit": 11000 <-- Should be 500. From JournalRecord
              },
              {
                "Name": "Accpar3",
                "Credit": 11000, <-- Should be 10000. From JournalRecord
                "Debit": 11000 <-- Should be 10000. From JournalRecord
              }
            ]
          }
        ]
      }
    ]
  }
]

这对我来说具有时间敏感性，任何指向正确方向的帮助都将对我有所帮助。

- lbrahim

---

你能展示一下你的代码所生成的JSON吗？ - Nate

@Nate 感谢您的关注。已在问题中发布了输出结果。 - lbrahim

由于您在各处使用了 g.Sum(x=>x)，因此您的输出是有意义的。我认为您需要在内部的 .Sum() 调用上添加一个 .Where() 子句，以仅匹配该帐户部分的记录。 - Nate

Answer 1

var records = db.JournalEntries.Include(je => je.JournalRecords.Select(jr => jr.Account).Select(j => j.AccountParticulars))
        .Where(je => je.Date >= from && je.Date <= to)
        .SelectMany(s => s.JournalRecords)
        .GroupBy(d => d.AccountParticular.Account.AccountGroup.AccountCategory)
        .Select(g => new
        {
            Name = g.Key.Name,
            Credit = g.Sum(c => c.Credit),
            Debit = g.Sum(d => d.Debit),
            AccountGroups = g.Key.AccountGroups.Select(ag => new
            {
                Name = ag.Name,
                Credit = ag.Sum(c => c.Credit), <--- CHANGED
                Debit = ag.Sum(d => d.Debit),   <--- CHANGED
                Accounts = ag.Accounts.Select(ac => new
                {
                    Name = ac.Name,
                    Credit = ac.Sum(c => c.Credit), <--- CHANGED
                    Debit = ac.Sum(d => d.Debit),   <--- CHANGED
                    AccountParticulars = ac.AccountParticulars.Select(ap => new 
                    {
                        Name = ap.Name,
                        Credit = ap.Sum(c => c.Credit), <--- CHANGED
                        Debit = ap.Sum(d => d.Debit)    <--- CHANGED
                    })
                })
            })
        });

- Michal Ciechan

---

谢谢您的回答。但是我不会在ag上得到一个Sum()，因为它指向一个AccountGroup对象。 - lbrahim

你能否发布包括导航属性在内的.NET模型？ - Michal Ciechan

Answer 2

你似乎在每个层级上都汇总了相同的值。

在账户明细级别上，看起来你应该汇总与个别账户明细相关的值，即：

AccountParticulars = ac.AccountParticulars.Select(ap => new 
{
    Name = ap.Name,
    Credit = g.Where(entry => ap.Id == entry.AccountParticularId).Sum(entry => entry.Credit),
    Debit = g.Where(entry => ap.Id == entry.AccountParticularId).Sum(entry => entry.Debit),
})

- RagtimeWilly