LINQ and group by data nested within a structure

c#, linq, nested

Solution

The technique to flatten a hierarchical structure is to use the `SelectMany` method. You need something like this:

var result = mainList.SelectMany(x => x.LineOfBusinessList)
                     .SelectMany(lob => lob.Watchlists)
                     .GroupBy(wl => wl.ID)
                     .Select(g => new { 
                            WatchlistID = g.Key,
                            WatchlistName = g.First().Name,
                            ReportCount = g.Sum(item => item.ReportCount)  
                     });

The first `SelectMany` call will transform the original list to sequence of all `LineOfBusiness` objects in all items. The second `SelectMany` call will transform a sequence of `LineOfBusiness` objects to a sequence containing all `Watchlist` objects it them. Then you group these `Watchlist`s by they `ID` and perform the actual query on them.

Problem

I have a structure that roughly looks like this: ``` List<ProductLine> -> ID Name ... List<LineOfBusiness> -> ID Name ... List<Watchlist> -> ID Name ReportCount ``` A Watchlist can exist under multiple LoBs but the ReportCount will only be for the count of reports that exist under that LoB for that WatchList. I need them in the structure this way because the count of how many reports exist within a LoB for a given Watchlist is important elsewhere. What I need to do is get a list of the distinct WatchLists (grouped based on ID) and have the ReportCount be the SUM of that watchlist's ReportCount across all LoBs. I can't quite get the nested select logic to work right.

Original source