Use LINQ to GroupBy two columns and build a dictionary with them?

Question

I am trying to use LINQ in order to build a record that is sorted by it's Store Preference and has a List of applications that are associated with each CompletionStatusFlag. In order to do this my first thought was to have the record hold a StorePreference, and then have a Dictionary that seperates the applications based on the CompletionStatus.

Employee Application

public class EmployeeApplication
{
     public int Id { get; set; }
     public StorePreference { get; set; }
     public CompletionStatusFlag CompletionStatus { get; set; }
}

ApplicationCountRecord

public class ApplicationCountRecord
{
    public Store StorePreference { get; set; }
    public Dictionary<CompletionStatusFlag, List<EmployeeApplication>> StatusApplicationPairs { get; set; } 
            = new Dictionary<CompletionStatusFlag, List<EmployeeApplication>>();
    public int TotalCount => StatusApplicationPairs.Count();
}

The problem arises when trying to create the dictionary. I have the completion status saved, but how do I get the current applications that match the completion status in order to pass them into the dictionary?

public void ConvertAppsToResults()
{
   var applications = _appRepo.Query()
       .GroupBy(x => new { x.StorePreference, x.CompletionStatus })
       .Select(y => new ApplicationCountRecord()
       {
            StorePreference = y.Key.StorePreference,
            //Somehow create dictionary here.
       });
}

Heres is another incorrect attempt that wont even compile, but it might help you see my thought process.

public void ConvertAppsToResults()
{
    //get apps in DateFilter range, and creates records seperated by StorePreference, and CompletionStatus
    var applications = _applicationRepo.Query()
                .GroupBy(x => new { x.StorePreference1, x.CompletionStatus })
                .Select(y => new ApplicationCountRecord()
                {
                    StorePreference = y.Key.StorePreference,
                    StatusApplicationPairs = new Dictionary<CompletionStatusFlag, List<EmployeeApplication>>()
                    .Add(y.Key.CompletionStatus, y.Where(x => x.CompletionStatus == y.Key.CompletionStatus).ToList());
                });
}

Answer 1

There's a double grouping going on here, which is possibly the source of your confusion

employeeApplications
  .GroupBy(ea => ea.Store)
  .Select(g => new ApplicationCountRecord()
    {
      StorePreference = g.Key
      StatusApplicationPairs = g.GroupBy(ea => ea.CompletionStatus).ToDictionary(g2 => g2.Key, g2 => g2.ToList())
    }
  )

Suppose you have 100 EmployeeApplications, across 10 Stores, and there are 5 statuses and 2 applications in each status. 2 apps * 5 statuses * 10 stores = 100 applications

GroupBy(Store) takes your list of e.g. 100 EmployeeApplications (10 per store) and groups it into 10 IGrouping that are conceptually each a list of 10 EmployeeApplications. 10 IGrouping for Store1, 10 IGrouping for Store2 etc..

Select runs over the 10 groupings, and on each one g (which, remember, behaves like a list of EmployeeApplications that all have the same Store, given in g.Key) it groups again by calling GroupBy(CompletionStatus) to further subdivide the 10 EAs in g on CompletionStatus. The 10 EAs for e.g. "Store 1" are divvied up into 5 IGroupings (5 statuses) that have 2 EAs in each inner grouping, then the same process is done for Store 2, 3 etc. There are thus 5 g2.Keys, one for each of the 5 statuses and there are 2 EAs in each g2

ToDictionary is called after it's grouped, so you get a dictionary with 5 keys and each key relates to a list of 2 applications. The ToDictionary takes two arguments; what to use for the key (g2.Key is a Status) and what to use for the value (g2.ToList() realizes a List<EmployeeApplication>)

---

= new Dictionary<CompletionStatusFlag, List<EmployeeApplication>>(); is unnecessary in AppCountRecord, as it will be replaced anyway