CODEWITHSUNDEEP
c#linq.net-coreentity-framework-core
Jordy van Eijk
Jordy van Eijk

Reputation: 2766

Howto do a group by and having in Linq

After watching a couple of questions about howto create a linq query that has a groupby and where, i tried it myself but had no luck.

I want to convert this SQL query to a corresponding linq query

select distinct city from zipcodedata 
group by city
having city like 'zw%'

This query is ready in about 0,03 seconds on my development machine with 8mln records in the table. But when i try to create a a linq query like this:

res = _context.Zipcodes
            .GroupBy(z => z.City)
            .Where(z => z.Key.Contains(query))
            .Select(z => z.Key)
            .ToList();

The sql profiler tells me the following query

SELECT [z].[Zipcode], [z].[Street], [z].[Streetnumber], [z].[City], [z]. [Latitude], [z].[Longitude], [z].[Municipality], [z].[Province]
FROM [ZipcodeData] AS [z]
ORDER BY [z].[City]

I dont see a where nor a having, can some explain me why this happens.

as a footnote im using Dotnet CORE 1.1.0 with EFCore 1.1.0

Upvotes: 0

Views: 182

Answers (1)

Jordy van Eijk
Jordy van Eijk

Reputation: 2766

After further investigation i found out that it is not yet possible in EF-Core according to the Feature Comparison between EF-Core and EF

It states Features not in EF Core --> Querying Data:

GroupBy translation in particular will move translation of the LINQ GroupBy operator to the database, rather than in-memory.

See this issue on Github exactly what i see when looking at the query in SQL Profiler :-(

I hope other people will find this answer useful

Upvotes: 2

Related Questions