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

Here is a simple example 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()};

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

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

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

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   gt  p Status    ProjectStatus Active       GroupBy f   gt  f Country       Select g   gt  new   country   g Key  count   g Count           ToDictionary k   gt  k country  i   gt  i count     Originally found here   http   www snippetsource net Snippet 140 groupby-and-count-with-ef-in-c

[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