The LINQ expression could not be translated.

[buddyackerman]

Steps to reproduce

The LINQ expression 'DbSet<MyEntity>() .Where(o => o.ScoringDate.Date > DateTime.Now.AddDays(-61).Date) .GroupBy(o => o.ServiceId)' could not be translated.

The issue

I recently upgraded my project from .NET Core 3.1 using EF 2.2.6 (Pomelo 2.2.6) to .NET 6 using EF6 (Pomelo 6). This same statement worked fine in the previous project version but now throws the above error in .NET/EF/Pomelo 6. I have other statement with different LINQ functions that are failing as well.

Further technical details

MySQL version: 8 Operating system: Windows x64 Pomelo.EntityFrameworkCore.MySql version: 6 Microsoft.AspNetCore.App version: 6

[mguinness]

Query in this .NET Fiddle generates the correct SQL.

[buddyackerman]

Follow-up, I tried bbreakinng up the statement to see where it would work and where it would break

var retVal1 = _context.MyEntity .Where(o => o.ScoringDate.Date > DateTime.Now.AddDays(-61).Date).AasEnumerable();

var retVal = retVal1.GroupBy(o => o.ServiceId);

This worked. Seems odd that the simple acct of doing a GroupBy caused the problem.

[buddyackerman]

Query in this .NET Fiddle generates the correct SQL.

I don't see any significant differences between your setup and mine but I still get the error when I try to log the querystring. Below is the configuration of my context class.

services.AddDbContext<MyContext>(options => options.UseMySql(databaseConnectionString, MySqlServerVersion.LatestSupportedServerVersion,(o) => { o.EnableRetryOnFailure(); }), ServiceLifetime.Transient);

[petergummer]

I seem to have run into the same problem when migrating a project to EF Core 6. In my case, I'm migrating from EF Core 5. Also, my query involves SelectMany rather than GroupBy. EF Core 6 can translate the expression if I remove '.Date' -- but of course I can't deploy with that change because it would return incorrect results. I have set the Npgsql.EnableLegacyTimestampBehavior switch, but I don't think it's related because I still get the error when I comment that out. Oh by the way, although we're not planning to upgrade to .NET 8 until early 2024, I've done an experimental upgrade. I still get this error with EF Core 8.

[lauxjpn]

@buddyackerman EF Core 2.2.6 was automatically falling back on client evaluation if a query could not be translated to valid SQL.

This behavior has changed with EF Core 3.0 for performance reasons. Now, you have to explicitly switch to client evaluation (e.g. by using .AsEnumerable()). If a query is translated that is not supported, you will now get an exception.

In your case, the general Linq GroupBy() call cannot be translated to a SQL statement. The SQL GROUP BY and the Linq GroupBy() differ significantly in how they work. There is only limited support in EF Core for Linq GroupBy() with a specific pattern. See Complex Query Operators: GroupBy in the EF Core docs for more information.

I don't see any significant differences between your setup and mine but I still get the error when I try to log the querystring.

The difference between the .NET Fiddle and your OP query is the .Select(i => i.Key) call in the Linq query, that makes it work.

[lauxjpn]

@petergummer Is your issue regarding Pomelo or Npgsql?