Efcore: Query with group by doesn't work after upgrading from ef core 2.2.6 to 3.1.1

[Martijn85]

I have a (rather extensive) query that I use to get a list with all customers and their contracts for customers that don't have an e-mail task assigned.
This query uses a group by, and after the upgrade from 2.2.6 to 3.1.1 this stopped working.
If I remove the group by part and do a loop with multiple queries, everything works. When I downgrade back to the old version, everything works as well.

Steps to reproduce

C# // All customers that don't have a task scheduled themselves List<IGrouping<Customer, Contract>> customerContracts = (from customer in this.Database.Customers join createReportTask in this.Database.SendCustomerReportTasks on customer.Id equals createReportTask.CustomerId into customerTasks from customerTask in customerTasks.DefaultIfEmpty() where customerTask.CustomerId == null join contract in this.Database.Contracts on customer.Id equals contract.CustomerId group contract by contract.Customer into customerWithContracts select customerWithContracts).ToList();

Error Message

System.InvalidOperationException: 'The LINQ expression 'DbSet
.LeftJoin(
outer: DbSet,
inner: c => (Nullable)c.Id,
outerKeySelector: s => s.CustomerId,
innerKeySelector: (c, s) => new TransparentIdentifier(
Outer = c,
Inner = s
))
.Where(ti => ti.Inner.CustomerId == null)
.Join(
outer: DbSet,
inner: ti => ti.Outer.Id,
outerKeySelector: c0 => c0.CustomerId,
innerKeySelector: (ti, c0) => new TransparentIdentifier, Contract>(
Outer = ti,
Inner = c0
))
.Join(
outer: DbSet,
inner: ti0 => EF.Property>(ti0.Inner, "CustomerId"),
outerKeySelector: c1 => EF.Property>(c1, "Id"),
innerKeySelector: (o, i) => new TransparentIdentifier, Contract>, Customer>(
Outer = o,
Inner = i
))
.GroupBy(
source: ti0 => ti0.Inner,
keySelector: ti0 => ti0.Outer.Inner)' 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 either AsEnumerable(), AsAsyncEnumerable(), ToList(), or ToListAsync(). See https://go.microsoft.com/fwlink/?linkid=2101038 for more information.'

Further technical details

EF Core version: 3.1.1
Database provider: Microsoft.EntityFrameworkCore.SqlServer
Target framework: .NET Framework 4.7.2
Operating system: Windows 10
IDE: Visual Studio 2019 16.3

[ErikEJ]

Group by was client evaluated in 2.2!

[dharmaturtle]

maumar had a good answer when I was confused by this.

[Martijn85]

Thank you for the support. By changing the group by to be evaluated locally I managed to solve the problem.
However, the expression might have changed in version 2.2, the code worked in 2.2.6. So, something else must have been changed as well in version >3.

For anybody else interested in the solution. This is what I came up with.

C# List<IGrouping<Customer, Contract>> customerContracts = (from contract in database.Contracts join customer in database.Customers on contract.CustomerId equals customer.Id into customers from customer in customers join sendReportTask in database.SendCustomerReportTasks on contract.CustomerId equals sendReportTask.CustomerId into tasks from task in tasks.DefaultIfEmpty() where task.CustomerId == null select new { Customer = customer, Contract = contract }) .ToList() .GroupBy(cc => cc.Customer, elementSelector: cc => cc.Contract) .ToList();