I need to translate this SQL statement to a Linq-Entity query...
SELECT name, count(name) FROM people
GROUP by name
5 Answers
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() });
Comments
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
5 Comments
Simon_Weaver
Also no grouping in 1.1
Simon_Weaver
or 1.2 or 2.0. I give up
Yush0
it is announced for 2.1
BornToCode
This 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.
Yul S
It was implemented in ef core 2. But I had problem with includes if I used other GroupBy than .GroupBy(x => x.OfferId, (key, list) =>....).
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
Comments
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]
1 Comment
user1735921
How to access this fields ? in var query ?
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() });
group by + join rows data, see stackoverflow.com/questions/12558509/…