LINQ to Object - How to implement dynamic SELECT projection of sub elements

Question

I have several classes of business logic:

public class Client {
    public string Code { get; set; } = string.Empty;
    public string Status { get; set; } = string.Empty;
    public string Account { get; set; } = string.Empty;
    public Total Total { get; set; } = new Total();
    public List<Month> Months { get; set; } = new List<Month>();
}

public class Month {
    public int Number { get; set; } = 0;
    public string Name { get; set; } = string.Empty;
    public DateTime Start { get; set; } = new DateTime();
    public DateTime End { get; set; } = new DateTime();
    public Total Summary { get; set; } = new Total();
}

public class Total {
    public int Count { get; set; } = 0;
    public decimal Sum { get; set; } = 0.0m;
}

which are instanced as follows:

List<Client> clients = new List<Client>() {
    new Client {
        Code = "7002.70020604",
        Status = "Active",
        Account = "7002.915940702810005800001093",
        Total = new Total {
            Count = 9,
            Sum = 172536.45m
        },
        Months = new List<Month>() {
            new Month {
                Number = 0,
                Name = "January",
                Start = new DateTime(2021, 1, 1, 0, 0, 0),
                End = new DateTime(2021, 1, 31, 23, 59, 59),
                Summary = new Total {
                    Count = 6,
                    Sum = 17494.50m
                }
            },
            new Month {
                Number = 1,
                Name = "February",
                Start = new DateTime(2021, 2, 1, 0, 0, 0),
                End = new DateTime(2021, 2, 28, 23, 59, 59),
                Summary = new Total {
                    Count = 3,
                    Sum = 155041.95m
                }
            },
            new Month {
                Number = 2,
                Name = "March",
                Start = new DateTime(2021, 3, 1, 0, 0, 0),
                End = new DateTime(2021, 3, 31, 23, 59, 59),
                Summary = new Total {
                    Count = 0,
                    Sum = 0.0m
                }
            }   
        }
    },
    new Client {
        Code = "7002.70020604",
        Status = "Active",
        Account = "7002.800540702810205800001093",
        Total = new Total {
            Count = 4,
            Sum = 16711.21m
        },
        Months = new List<Month>() {
            new Month {
                Number = 0,
                Name = "January",
                Start = new DateTime(2021, 1, 1, 0, 0, 0),
                End = new DateTime(2021, 1, 31, 23, 59, 59),
                Summary = new Total {
                    Count = 0,
                    Sum = 0.0m
                }
            },
            new Month {
                Number = 1,
                Name = "February",
                Start = new DateTime(2021, 2, 1, 0, 0, 0),
                End = new DateTime(2021, 2, 28, 23, 59, 59),
                Summary = new Total {
                    Count = 0,
                    Sum = 0.0m
                }
            },
            new Month {
                Number = 2,
                Name = "March",
                Start = new DateTime(2021, 3, 1, 0, 0, 0),
                End = new DateTime(2021, 3, 31, 23, 59, 59),
                Summary = new Total {
                    Count = 4,
                    Sum = 16711.21m
                }
            }   
        }
    }
};

I'm trying to arrange aggregate data of a view like this:

+---------------+--------+------------------+-------------------+------------------+-------------------+
|      Code     | Status |      January     |      February     |       March      |       Total       |
|               |        +-------+----------+-------+-----------+-------+----------+-------+-----------+
|               |        | Count |    Sum   | Count |    Sum    | Count |    Sum   | Count |    Sum    |
+---------------+--------+-------+----------+-------+-----------+-------+----------+-------+-----------+
| 7002.70020604 | Active |   6   | 17494.50 |   3   | 155041.95 |   4   | 16711.21 |   13  | 189247.66 |
+---------------+--------+-------+----------+-------+-----------+-------+----------+-------+-----------+

using projection like this:

clients
    .GroupBy(x => x.Code)
    .Select(y => new {
        Code = y.First().Code,
        Status = y.First().Status,
        Account = y.First().Account,
        Total = new {
            Count = y.Sum(z => z.Total.Count),
            Sum = y.Sum(z => z.Total.Sum)
        },
        Months = new {
            /*
                ?
            */
        }
    });

But I can't project the data by month. Assuming the date range (months) can be more than just this example. Please help!

Full interactive code listing at dotnetfiddle

Answer 1

You can use SelectMany to get months out of y and then group by month similarly as you group by code:

//...

Months = y
    .SelectMany(client => client.Months)
    .GroupBy(month => month.Name, (_, months) => new {
        Number = months.First().Number,
        Name = months.First().Name,
        Start = months.First().Start,
        End = months.First().End,
        Summary = new {
            Count = months.Sum(z => z.Summary.Count),
            Sum = months.Sum(z => z.Summary.Sum)
        }
    }).ToList()

//...

That being said I don't suggest to use y.First() or months.First() more than once in each function because it makes an enumeration each time it is used. The following should in general have better performance:

(_, months) => {
    var month = months.First();
    return new {
        Number = month.Number,
        Name = month.Name,
        Start = month.Start,
        End = month.End,
        Summary = new {
            Count = months.Sum(z => z.Summary.Count),
            Sum = months.Sum(z => z.Summary.Sum)
        }
    }
}

which is also not ideal because we're still making 3 enumerations here (1 enumeration in .First() and 1 enumeration for every .Sum(...)).

Even better approach would be to use Aggregate function which will do only a single enumeration:

(_, months) => months
    .Aggregate((res, nextVal) => new Month {
        Number = nextVal.Number,
        Name = nextVal.Name,
        Start = nextVal.Start,
        End = nextVal.End,
        Summary = new Total {
            Count = res.Summary.Count + nextVal.Summary.Count,
            Sum = res.Summary.Sum + nextVal.Summary.Sum
        }
    })

Answer 2

This LINQ query should prepare data for visualization:

clients
    .GroupBy(x => new {x.Code, x.Status})
    .Select(g => new 
    {
        Code = g.Key
        MonthsSummary = g.SelectMany(x => x.Months)
            .OrderBy(x => x.Start)
            .GroupBy(x => new {x.Start, x.Name})
            .Select(gm => new 
            {
                gm.Key.Name,
                Count = gm.Sum(x => x.Summary.Count),
                Sum = gm.Sum(x => x.Summary.Sum),
            })
            .ToList()
    });