Create JSON with LINQ

Question

This is the table structure I have: mytablesctructure

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.

@Nate Thanks for your interest. Posted the output in question. — lbrahim
– lbrahim, Commented Mar 3, 2015 at 22:48
Your output makes sense, since you are using g.Sum(x=>x) everywhere. I think you need to add a .Where() clause on your inner .Sum() calls to only match records for that Account Particular. — Nate
– Nate, Commented Mar 3, 2015 at 22:54

Michal Ciechan · Accepted Answer · 2015-03-03 23:26:33Z

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

Thanks for your answer. But I won't get a Sum() on ag for example because that is pointing to a AccountGroup object.
could you post your .NET models including the navigation properties?

RagtimeWilly · Accepted Answer · 2015-03-03 23:32:37Z

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

edited Mar 3, 2015 at 23:32

answered Mar 3, 2015 at 23:04

RagtimeWilly

5,4753 gold badges30 silver badges42 bronze badges

Collectives™ on Stack Overflow

Create JSON with LINQ

2 Answers 2

2 Comments

Comments

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Related