Groupby in relationtable

Question

I am creating some tag functionality for a forum using linq2sql, and I have two tables

[Tag]

• TagId
• TagName

[ForumTagRelation]

• TagId
• ForumId

I would like to retrieve, like SO, the most popular tags.

I have tried to do this by:

 List<Tag> popularTags = db.Tags.Select(x => x.ForumTagRelations.GroupBy(y => y.TagId).OrderByDescending(z => z.Count())).Take(count).ToList();

But this just returns the following error:

Error   1   Cannot implicitly convert type 'System.Collections.Generic.List<System.Linq.IOrderedEnumerable<System.Linq.IGrouping<System.Guid?,SampleWebsite.ForumTagRelation>>>' to 'System.Collections.Generic.IEnumerable<SampleWebsite.Tag>'. An explicit conversion exists (are you missing a cast?)    

The question is how I easily can return a list of tags which has the most counts in the ForumTagRelation table?

Answer 1

This should be it:

List<Tag> popularTags = (db.Tags.Where(t => db.ForumTagRelations.GroupBy(gbftr => gbftr.TagID).OrderByDescending(obftr => obftr.Count()).Take(count).Any(ftr => ftr.Key == t.TagID)).Take(count)).ToList();