SQL to Entity Framework Count Group-By

count entity-framework group-by linq-to-entities sql


I need to translate this SQL statement to a Linq-Entity query...

SELECT name, count(name) FROM people
GROUP by name
7/19/2012 3:47:27 PM

Accepted Answer

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() });
7/19/2012 3:43:31 PM

Popular Answer

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.


Related Questions


Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow