How to group using LINQ, then get value of largest group

Question

Here's what I have so far:

  var bestReason =
   from p in successfulReasons
   group p by p.Reason into g
   select new { Excuse = g.Key, ExcuseCount = g.Count() };

What I need to do now is return one reason that is the best reason, determined by which were successful in the past.

Sample data:

ID,Reason
---------
0,Weather
1,Traffic
2,Illness
3,Weather
4,Traffic
5,Traffic
6,Pirates

should return "Traffic"

Would like to do it all in one LINQ statement, if possible.

Thanks.

EDIT: If there are 7 Pirate Attacks, and 7 Traffic Accidents, I'm ok with returning either one (the first alphabetically would be fine).

Answer 1

var bestReason = successfulReasons
    .GroupBy(r => r.Reason)
    .OrderByDescending(grp => grp.Count())
    .First().Key;

Answer 2

If I understand your question correctly, you can do:

string bestReason =
    (from p in successfulReasons
     orderby p.Reason
     group p by p.Reason into g
     orderby g.Count() descending
     select g.Key).FirstOrDefault();

Answer 3

 var group = excuses.GroupBy(m => m.Reason)
      .OrderByDescending(m => m.Count())
      .Select(m => m.Key)
      .FirstOrDefault();

Which produces the following sql statement:

SELECT TOP (1) [t1].[Reason]
FROM (
    SELECT COUNT(*) AS [value], [t0].[Reason]
    From [dbo].[Excuses] As [t0]
    GROUP BY [t0].[Reason]
    ) As [t1]
ORDER BY [t1].[value] DESC

Since this is a moderately complicated IQueryable expression, you might consider compiling it to speed up the response time:

Func<ExcusesDataContext, string> commonResult = CompiledQuery.Compile(
    (ExcusesDataContext c) => c.Excuses.GroupBy(m => m.Reason).OrderByDescending(m => m.Count()).Select(m => m.Key).FirstOrDefault()
);

Console.WriteLine(commonResult(new ExcusesDataContext()));
Console.ReadLine();

You could also just call the stored procedure via a repository and snag the particular value that you're looking for. This would be the fastest path to happiness, but the least fun to maintain:

string excuse = this.repo.Excuses.MostCommonFor(ProblemList.BeingLate);