How to group using LINQ, then get value of largest group
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 wit开发者_如何学JAVAh returning either one (the first alphabetically would be fine).
var bestReason = successfulReasons
.GroupBy(r => r.Reason)
.OrderByDescending(grp => grp.Count())
.First().Key;
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();
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);
精彩评论