SQL to Entity Framework Count Group-By
Solution 1
Query syntax
var query = from p in context.People
group p by p.name into g
select new
{
name = g.Key,
count = g.Count()
};
Method syntax
var query = context.People
.GroupBy(p => p.name)
.Select(g => new { name = g.Key, count = g.Count() });
Solution 2
Edit: EF Core 2.1 finally supports GroupBy
But always look out in the console / log for messages. If you see a notification that your query could not be converted to SQL and will be evaluated locally then you may need to rewrite it.
Entity Framework 7 (now renamed to Entity Framework Core 1.0 / 2.0) does not yet support GroupBy()
for translation to GROUP BY
in generated SQL (even in the final 1.0 release it won't). Any grouping logic will run on the client side, which could cause a lot of data to be loaded.
Eventually code written like this will automagically start using GROUP BY, but for now you need to be very cautious if loading your whole un-grouped dataset into memory will cause performance issues.
For scenarios where this is a deal-breaker you will have to write the SQL by hand and execute it through EF.
If in doubt fire up Sql Profiler and see what is generated - which you should probably be doing anyway.
https://blogs.msdn.microsoft.com/dotnet/2016/05/16/announcing-entity-framework-core-rc2
Solution 3
A useful extension is to collect the results in a Dictionary
for fast lookup (e.g. in a loop):
var resultDict = _dbContext.Projects
.Where(p => p.Status == ProjectStatus.Active)
.GroupBy(f => f.Country)
.Select(g => new { country = g.Key, count = g.Count() })
.ToDictionary(k => k.country, i => i.count);
Originally found here: http://www.snippetsource.net/Snippet/140/groupby-and-count-with-ef-in-c
Solution 4
Here are simple examples of group-by in .NET Core 2.1:
var query = this.DbContext.Notifications
.Where(n => n.Sent == false)
.GroupBy(n => new { n.AppUserId })
.Select(g => new { AppUserId = g.Key, Count = g.Count() });
var query2 = from n in this.DbContext.Notifications
where n.Sent == false
group n by n.AppUserId into g
select new { id = g.Key, Count = g.Count()};
Both of these translate to:
SELECT [n].[AppUserId], COUNT(*) AS [Count]
FROM [Notifications] AS [n]
WHERE [n].[Sent] = 0
GROUP BY [n].[AppUserId]
Solution 5
with EF 6.2 it worked for me
var query = context.People
.GroupBy(p => new {p.name})
.Select(g => new { name = g.Key.name, count = g.Count() });
Related videos on Youtube
fefwfefefwfwe
Updated on March 19, 2022Comments
-
fefwfefefwfwe about 2 years
I need to translate this
SQL
statement to aLinq-Entity
query...SELECT name, count(name) FROM people GROUP by name
-
yu yang Jian almost 3 yearsif anyone want to
group by + join rows data
, see stackoverflow.com/questions/12558509/…
-
-
Jacob Stamm over 7 yearsThanks for the heads up
-
aruno over 7 yearsAlso no grouping in 1.1
-
aruno over 6 yearsor 1.2 or 2.0. I give up
-
Yush0 over 6 yearsit is announced for 2.1
-
BornToCode over 3 yearsThis can be misleading, I think it's important to update your answer and mention explicitly that EF versions earlier than EF 7 do support grouping. This answer which is more of a comment than an actual answer to the OP question is misleading when read by itself (and is interpreted as answer to the OP which is not). When reading this, one could get the wrong impression as if even EF 7 don't support grouping and obviously earlier versions don't support it which is just not true.