Q:

How do I convert a nested SQL select statement with multiple aggregate functions into LINQ?

I have a table where each row contains a team id, a user id and a boolean value indicating whether or not that user is a representative. Users can be representatives for more than one team and teams can have more than one representative. I'm trying to find out who is the most prolific representative and how many teams they are a representative for. Actually, I don't really care who it is, just how many teams they are a representative for.
I can do this easily in SQL with the below but I can't figure out how to write this in LINQ. I'd like to avoid a stored procedure if possible since they are very rarely used in this application and I'd like to maintain consistency.
SELECT MAX(a.RepCount) as TeamMaxRepCount
FROM
(SELECT DelegateUserID, COUNT(*) AS RepCount
FROM TeamDelegates
GROUP BY DelegateUserID) as a

A:

The other way round. E.g.:
var x = TeamDelegates.GroupBy(t => t.DelegateUserID)
                     .Select(t => new {
                                          DelegateUserID = t.Key,
                                          RepCount = t.Count()
                                      })
                     .Max(t => t.RepCount);

Which is the exact representation of your SQL query. This could even be simplyfied like sa_ddam213 mentioned in his comment.

