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

Question

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

maybe: int result = TeamDelegates.GroupBy(x => x.DelegateUserID).Max(g => g.Count()); — sa_ddam213
– sa_ddam213, Commented May 17, 2013 at 0:01

Linus Caldwell · Accepted Answer · 2013-05-17 00:00:40Z

2

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.

answered May 17, 2013 at 0:00

Linus Caldwell

11.1k12 gold badges48 silver badges61 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

recursive Over a year ago

Shouldn't that be DelegateUserID = t.Key?

Linus Caldwell Over a year ago

@recursive, of course it should! so, why didn't you write it that way?? ;-) Thank you!

Collectives™ on Stack Overflow

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

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related