LINQ not trivial Group By

Question

I have the following classes (omit syntax errors please)

class Message {
   public Person Recipient
   ...
}

class Person {
   public List<Group> Groups
   ...
}

class Group {
   public GroupTypesEnum GroupType
   ...
}

I want to generate a count of messages by group type. Example:

1. Message "hello", to "Person A", which is in "Group X" (type T1), "Group Y" (type T2)
2. Message "blah", to "Person B", which is in "Group Z" (type T1)

The query that I need would output:

Group type: T1, message count: 2
Group type: T2, message count: 1

How can I achieve this in Linq To Entities?
considering that a person might be in zero groups

I know how to achieve this in memory, but I want to use Linq-To-Entities:

Dictionary<GroupTypesEnum, int> count = new ...

foreach (Message msg in db.Messages) {
    foreach (Group group in msg.Recipient.Groups) {
         count[group.GroupType]++;
    }
}

Answer 1

This is not so hard with SelectMany

  var result = messages
    .SelectMany(m => m.Recipient.Groups)
    .GroupBy(t => t.GroupType)
    .Select(g => new { gType = g.Key, count = g.Count()});

Then result.Dump() - in LinqPad

Full source for my test code is here: https://gist.github.com/hoganlong/5841555