Selecting Grouped Items in EF Core

Question

This worked in EF 6.4:

from a in Addresses
group a by new {a.StreetName, a.StreetNumber} into agrp
where agrp.Count() > 3
from aitem in agrp
select aitem

If EF Core 5 I get:

InvalidOperationException: The LINQ expression 'agrp => agrp' could not be translated. Either rewrite the query in a form that can be translated, or switch to client evaluation explicitly by inserting a call to 'AsEnumerable', 'AsAsyncEnumerable', 'ToList', or 'ToListAsync'. See https://go.microsoft.com/fwlink/?linkid=2101038 for more information.

Why? Is there a different way to write this?

Answer 1

If you're OK with the data being loaded into memory, a simple solution could be to add .ToList() or .AsEnumerable() after Addresses:

from a in Addresses.ToList() // or .AsEnumerable()
group a by new {a.StreetName, a.StreetNumber} into agrp
where agrp.Count() > 3
from aitem in agrp
select aitem

Note that this (in SqlServer) translates into:

SELECT [a].[Id], [a].[StreetName], [a].[StreetNumber]
FROM [Addresses] AS [a]

In EF Core, GroupBy is (in many cases) not translated to SQL, but is run in memory. (To avoid accidentally loading a lot of data into memory, EF will throw an exception unless .ToList() or .AsEnumerable() is called to indicate that this is intentional.)

(...) Since no database structure can represent an IGrouping, GroupBy operators have no translation in most cases. When an aggregate operator is applied to each group, which returns a scalar, it can be translated to SQL GROUP BY in relational databases. (...)

- Complex query operators, GroupBy

The article also has an example of a query which translates into group by with a filter on Count (included below). The example doesn't fully cover the example in the question, unfortunately. It would not return the relevant Address-objects, only the group-by Key and Count.

var query = from p in context.Set<Post>()
            group p by p.AuthorId into g
            where g.Count() > 0
            orderby g.Key
            select new
            {
                g.Key,
                Count = g.Count()
            };

SELECT [p].[AuthorId] AS [Key], COUNT(*) AS [Count]
FROM [Posts] AS [p]
GROUP BY [p].[AuthorId]
HAVING COUNT(*) > 0
ORDER BY [p].[AuthorId]

Answer 2

This seems to work:

var addressGroupQuery = from a in Addresses
                        group a by new {a.StreetName, a.StreetType} into agrp
                        where agrp.Count() > 3
                        select agrp.Max(a => a.AddressID);
                
var addresses = from a in Addresses
                where addressGroupQuery.Contains(a.AddressID)
                select a;

Note the addressGroupQuery remains a deferred query (no ToList). Generates some clean looking SQL too:

SELECT [a].[AddressID], [a].[City], [a].[Country], [a].[StreetName], [a].[StreetNumber], [a].[StreetType], [a].[UnitNumber]
FROM [Address] AS [a]
WHERE EXISTS (
    SELECT 1
    FROM [Address] AS [a0]
    GROUP BY [a0].[StreetName], [a0].[StreetType]
    HAVING (COUNT(*) > 3) AND (MAX([a0].[AddressID]) = [a].[AddressID]))