LINQ - sum of column in hierarchical data

Question

My data looks like this:

I am trying to create a JSON ouput (using JSON .NET) which will have the sum of the Value column by levels.

For example:

{
'id': 'AB',
'sum': '53', 
'level2' : [
   {
     'id' : 'CD',
     'sum' : '23',
     'level3' : [
        {
          'id' : 'd1',
          'sum' : '12'
        }, 
        {
          'id' : 'd2',
          'sum' : '11'
        }
      ]
...

I am trying to use LINQ to create this. So far I have the following code:

var query = reader.SelectRows(r => new
    {
        level1 = r["level1"].ToString(),
        sum = r["sum"] != DBNull.Value ? Convert.ToDouble(r["sum"]) : 0,
        level2 = new
        {
            level2 = r["level2"].ToString(),
            sum = r["sum "] != DBNull.Value ? Convert.ToDouble(r["sum"]) : 0,
            level3 = new
            {
                level3 = r["level3 "].ToString(),
                sum = r["sum"] != DBNull.Value ? Convert.ToDouble(r["sum"]) : 0
            }
        }
    })
        .GroupBy(r => new { r.level1 })
        .Select(g => new
        {
            id = g.Key.level1,
            sum = g.Sum(x => x.sum),
            level2 = g.GroupBy(l => new { l.level2.level2 })
            .Select(l => new
            {
                id = l.Key.level2,
                sum = g.Sum(y => y.sum),
                level3 = l.GroupBy(m => new { m.level2.level3.level3 })
                .Select(m => new
                {
                    id = m.Key.level3,
                    sum = g.Sum(z => z.sum),
                })
            })
        });

retJSON = JsonConvert.SerializeObject(new { data = query }, Formatting.Indented);

The SelectRows function is like this:

// Adapted from this answer https://stackoverflow.com/a/1202973
// To https://stackoverflow.com/questions/1202935/convert-rows-from-a-data-reader-into-typed-results
// By https://stackoverflow.com/users/3043/joel-coehoorn
public static IEnumerable<T> SelectRows<T>(this IDataReader reader, Func<IDataRecord, T> select)
{
    while (reader.Read())
    {
        yield return select(reader);
    }
}

However, I am getting sum repeated at every level, i.e. the same value. Any direction on how to achieve this will be greatly appreciated.

score 0 · Accepted Answer · answered Nov 26 '19 at 13:22

I think your query is nearly there just 2 changes (shown by comments below).

.GroupBy(r => new { r.level1 })
    .Select(g => new
    {
        id = g.Key.level1,
        sum = g.Sum(x => x.sum),
        level2 = g.GroupBy(l => new { l.level2.level2 })
        .Select(l => new
        {
            id = l.Key.level2,
            sum = l.Sum(y => y.sum), //l not g
            level3 = l.GroupBy(m => new { m.level2.level3.level3 })
            .Select(m => new
            {
                id = m.Key.level3,
                sum = m.Sum(z => z.sum), //m not g
            })
        })
    });

Thanks! I looked at it for almost an hour and couldn't see it :( — KalC, Nov 26 '19 at 14:45

LINQ - sum of column in hierarchical data

1 Answers1