SQL to Entity Framework Count Group-By

Question

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

SELECT name, count(name) FROM people
GROUP by name

Answer 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() });

Answer 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

Answer 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

Answer 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]

Answer 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() });