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

User · Answer

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

User · 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  https   blogs msdn microsoft com dotnet 2016 05 16 announcing-entity-framework-core-rc2

User · 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   gt  p name                      Select g   gt  new   name   g Key  count   g Count

User · Answer

Here is a simple example of group by in  net core 2 1  var query   this DbContext Notifications              Where n  gt  n Sent    false               GroupBy n   gt  new   n AppUserId                 Select g   gt  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       Which translates to   SELECT  n   AppUserId   COUNT    AS  Count  FROM  Notifications  AS  n  WHERE  n   Sent    0 GROUP BY  n   AppUserId

User · Answer

with EF 6 2 it worked for me     var query   context People                 GroupBy p   gt  new  p name                   Select g   gt  new   name   g Key name  count   g Count

[sql] SQL to Entity Framework Count Group-By

Examples related to sql

Examples related to entity-framework

Examples related to linq-to-entities

Examples related to count

Examples related to group-by