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

Borrell

if anyone want to group by + join rows data, see #12559009 – Chokebore

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

Lobster

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

Woald

Thanks for the heads up – Velate

Also no grouping in 1.1 – Woald

or 1.2 or 2.0. I give up – Woald

it is announced for 2.1 – Hatchment

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. – Coalition

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) =>....). – Ptarmigan

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

Douse

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]

Jillayne

How to access this fields ? in var query ? – Caryophyllaceous

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

Bahadur