Create JSON with LINQ

Question

This is the table structure I have:

And I have to produce the following 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
                        }
                    ]
                }
            ]
        }
    ]
}

I have to start from JournalRecord and create the JSON output as I go up the chain. This is what I could muster so far but as can be seen it is not because I am summing the Credit and Debit value all the way so it is being same and not accordingly:

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

This is what my code produces:

[
  {
    "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
              }
            ]
          }
        ]
      }
    ]
  }
]

This is time sensitive for me so any help to right direction will help.

Answer 1

You look to be summing the same value at every level.

At the account particulars level it looks like you should be summing the values that relate to the individual account particular, i.e:

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

Answer 2

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