Linq pivot indicator values into additional columns and aggregate data

Question

I am trying to aggregate my data futher using Linq. I have a students table that was not designed properly which contains unnecessary duplications. I have fetched the data and I am trying to aggregate it to be readable easily. The existing structure of the data is as the following:

| Course | Class  | Sex | Count |
---------------------------------
| Math   | Junior | M   | 20    |
| Math   | Junior | F   | 15    |
| Other  | Other  | M   | 10    |
| Other  | Other  | F   | 15    |

I am trying to convert the above to an easier structure

| Course | Class  | Male| Female|
---------------------------------
| Math   | Junior | 20  | 15    |
| Other  | Other  | 10  | 15    |

I was using a for loop to get distinct courses and fetch records by sex and merge them into a record, but that's not really efficient. Does any one now a way to achieve that with Linq query, I thought it could be handled by Linq.

Answer 1

You can do this in one go in Linq, by Grouping by Course and Class (I've used a ValueTuple, but an anonymous Class grouping key is also OK).

The sums are then obtained by filtering and summing up the Males and Females:

var results = courseCounts
     .GroupBy(cc => (cc.Course, cc.Class))
     .Select(grp => new 
             {
                 Course = grp.Key.Course,
                 Class = grp.Key.Class,
                 Male = grp.Where(x => x.Sex == "M").Sum(x => x.Count),
                 Female = grp.Where(x => x.Sex == "F").Sum(x => x.Count)
             });

(Minor, but from a performance point of view on larger data sets, it's a bit inefficient to repeat the .Where(x => x.Sex == "x") twice - we could for instance key the data in a Grouping + Dictionary first)