How to group LINQ and then get the value of the largest group

Question

Here is what I still have:

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

Now I need to return to one of the reasons, which is the best reason determined by what has been 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"

I would like to do all this in a single LINQ statement, if possible.

Thanks.

EDIT: If there are 7 pirate attacks and 7 traffic accidents, I am fine with returning one of them (the first in alphabetical order will 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(); 

What creates 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 complex 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 can also just call the stored procedure through the repository and hook the specific value you are looking for. This would be the fastest path to happiness, but the least pleasure to maintain:

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