Pomelo.entityframeworkcore.mysql: The LINQ expression 'ROW_NUMBER() OVER(PARTITION BY f.SchoolId ORDER BY f.Id ASC)' could not be translated
Steps to reproduce
public class FunctionRole:AuditableEntity
{
public string Name { get; set; }
public string Description { get; set; }
public Guid SchoolId { get; set; }
public virtual School School { get; set; }
}
public class School:AuditableEntity
{
public bool IsRegistered { get; set; }
public bool IsEnabled { get; set; }
public string Name { get; set; }
public string Email { get; set; }
public string Website { get; set; }
public string PhoneNumber { get; set; }
public string ContactPerson { get; set; }
public string ActivationCode { get; set; }
public virtual ICollection<FunctionRole> FunctionRoles { get; set; }
public virtual ICollection<DocumentTemplate> DocumentTemplates { get; set; }
}
var query =
from s in dbContext.Set<Domain.Model.School>()
where s.Id == schoolId
from r in s.FunctionRoles
.Where(b => b.Name == FunctionRolesEnum.Manager.ToString())
.Take(1).DefaultIfEmpty()
select new SchoolSummaryDto
{
//... Some other properties
DocumentTemplates = s.DocumentTemplates
.Select(a => new DocumentTemplateDto
{
Id = a.Id,
Description = a.Description,
SchoolId = a.SchoolId,
FileName = a.FileName,
DocumentTemplateTypeId = a.DocumentTemplateTypeId
})
.ToList(),
Signers = s.Employees
.Where(e => e.Status != PersistentStatusEnum.Removed
&& e.FunctionRoleId == r.Id)
.Select(a => new NameValueType
{
Id = a.Id,
Name = a.FirstName + " " + a.MiddleName + " " + a.LastName
})
.ToList(),
ContactPerson = s.ContactPerson,
Email = s.Email,
PhoneNumber = s.PhoneNumber,
SchoolId = s.Id,
SchoolName = s.Name,
Website = s.Website,
IsEnabled = s.IsEnabled,
IsRegistered = s.IsRegistered
};
var school = query.FirstOrDefault();
The issue
The section
Signers = s.Employees
.Where(e => e.Status != PersistentStatusEnum.Removed
&& e.FunctionRoleId == r.Id)
.Select(a => new NameValueType
{
Id = a.Id,
Name = a.FirstName + " " + a.MiddleName + " " + a.LastName
})
.ToList(),
is causing an exception:
System.InvalidOperationException: 'The LINQ expression 'ROW_NUMBER() OVER(PARTITION BY f.SchoolId ORDER BY f.Id ASC)' 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.
Further technical details
MySQL version: 8.0.22
Operating system: Windows 10 (64-bit)
Pomelo.EntityFrameworkCore.MySql version: 5.0.0-alpha.2
Microsoft.AspNetCore.App version: 5.0.0
Other details about my project setup:
Migration from .Net Core 2.2 to .Net Core 5.0
FaizanMubasher
All 9 comments
This works totally fine for me. Tested with the following code:
Program.cs
```c#
using System;
using System.Collections.Generic;
using System.Diagnostics;
using System.Linq;
using Microsoft.EntityFrameworkCore;
using Microsoft.Extensions.Logging;
using Pomelo.EntityFrameworkCore.MySql.Infrastructure;
namespace IssueConsoleTemplate
{
public enum PersistentStatusEnum
{
Valid,
Removed,
}
public enum FunctionRolesEnum
{
Manager,
}
public class AuditableEntity
{
public Guid Id { get; set; }
public PersistentStatusEnum Status { get; set; }
}
public class FunctionRole : AuditableEntity
{
public string Name { get; set; }
public string Description { get; set; }
public Guid SchoolId { get; set; }
public virtual School School { get; set; }
}
public class Employee : AuditableEntity
{
public string FirstName { get; set; }
public string MiddleName { get; set; }
public string LastName { get; set; }
public Guid SchoolId { get; set; }
public Guid FunctionRoleId { get; set; }
public virtual School School { get; set; }
public virtual FunctionRole FunctionRole { get; set; }
}
public class School : AuditableEntity
{
public string Name { get; set; }
public virtual ICollection<Employee> Employees { get; set; }
public virtual ICollection<FunctionRole> FunctionRoles { get; set; }
}
public class Context : DbContext
{
public virtual DbSet<School> Schools { get; set; }
public virtual DbSet<FunctionRole> FunctionRoles { get; set; }
public virtual DbSet<Employee> Employees { get; set; }
protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
{
var connectionString = "server=127.0.0.1;port=3306;user=root;password=;database=Issue1288";
optionsBuilder.UseMySql(
connectionString,
ServerVersion.AutoDetect(connectionString),
options => options.CharSetBehavior(CharSetBehavior.NeverAppend))
// optionsBuilder.UseSqlServer(@"Data Source=.\MSSQL14;Integrated Security=SSPI;Initial Catalog=Issue1288")
.UseLoggerFactory(
LoggerFactory.Create(
configure => configure
.AddConsole()
.AddFilter(level => level >= LogLevel.Information)))
.EnableSensitiveDataLogging()
.EnableDetailedErrors();
}
protected override void OnModelCreating(ModelBuilder modelBuilder)
{
modelBuilder.Entity<School>()
.HasData(
new School
{
Id = new Guid("67FBF467-E8BB-40D8-BDEA-C3A965204D77"),
Status = PersistentStatusEnum.Valid,
Name = "EF Core School",
});
modelBuilder.Entity<Employee>()
.HasData(
new Employee
{
Id = new Guid("CE710A03-DE94-4B58-97A9-3030DDFE2616"),
Status = PersistentStatusEnum.Valid,
FirstName = "John",
LastName = "Doe",
SchoolId = new Guid("67FBF467-E8BB-40D8-BDEA-C3A965204D77"),
FunctionRoleId = new Guid("F0FB348D-0905-4895-86B8-DCB9E0672F21"),
});
modelBuilder.Entity<FunctionRole>()
.HasData(
new FunctionRole
{
Id = new Guid("F0FB348D-0905-4895-86B8-DCB9E0672F21"),
Status = PersistentStatusEnum.Valid,
Name = "Manager",
SchoolId = new Guid("67FBF467-E8BB-40D8-BDEA-C3A965204D77"),
});
}
}
public class NameValueType
{
public Guid Id { get; set; }
public string Name { get; set; }
}
public class SchoolSummaryDto
{
public Guid SchoolId { get; set; }
public string SchoolName { get; set; }
public ICollection<NameValueType> Signers { get; set; }
}
internal static class Program
{
private static void Main(string[] args)
{
using var dbContext = new Context();
dbContext.Database.EnsureDeleted();
dbContext.Database.EnsureCreated();
var schoolId = new Guid("67FBF467-E8BB-40D8-BDEA-C3A965204D77");
var query =
from s in dbContext.Set<School>()
where s.Id == schoolId
from r in s.FunctionRoles
.Where(b => b.Name == FunctionRolesEnum.Manager.ToString())
.Take(1)
.DefaultIfEmpty()
select new SchoolSummaryDto
{
Signers = s.Employees
.Where(e => e.Status != PersistentStatusEnum.Removed
&& e.FunctionRoleId == r.Id)
.Select(a => new NameValueType
{
Id = a.Id,
Name = a.FirstName + (string.IsNullOrEmpty(a.MiddleName) ? null : " " + a.MiddleName) + " " + a.LastName
})
.ToList(),
SchoolId = s.Id,
SchoolName = s.Name,
};
var school = query.FirstOrDefault();
Trace.Assert(school != null);
Trace.Assert(school.SchoolName == "EF Core School");
Trace.Assert(school.Signers.Count == 1);
Trace.Assert(school.Signers.First().Name == "John Doe");
}
}
}
</details>
<details>
<summary>Console output (contains generated SQL)</summary>
warn: Microsoft.EntityFrameworkCore.Model.Validation[10400]
Sensitive data logging is enabled. Log entries and exception messages may include sensitive application data; this mode should only be enabled during development.
info: Microsoft.EntityFrameworkCore.Infrastructure[10403]
Entity Framework Core 5.0.1 initialized 'Context' using provider 'Pomelo.EntityFrameworkCore.MySql' with options: ServerVersion 8.0.21-mysql SensitiveDataLoggingEnabled DetailedErrorsEnabled
info: Microsoft.EntityFrameworkCore.Database.Command[20101]
Executed DbCommand (6ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
CREATE DATABASE Issue1288;
info: Microsoft.EntityFrameworkCore.Database.Command[20101]
Executed DbCommand (66ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
CREATE TABLE Schools (
Id char(36) NOT NULL,
Name longtext NULL,
Status int NOT NULL,
CONSTRAINT PK_Schools PRIMARY KEY (Id)
);
info: Microsoft.EntityFrameworkCore.Database.Command[20101]
Executed DbCommand (31ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
CREATE TABLE FunctionRoles (
Id char(36) NOT NULL,
Name longtext NULL,
Description longtext NULL,
SchoolId char(36) NOT NULL,
Status int NOT NULL,
CONSTRAINT PK_FunctionRoles PRIMARY KEY (Id),
CONSTRAINT FK_FunctionRoles_Schools_SchoolId FOREIGN KEY (SchoolId) REFERENCES Schools (Id) ON DELETE CASCADE
);
info: Microsoft.EntityFrameworkCore.Database.Command[20101]
Executed DbCommand (36ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
CREATE TABLE Employee (
Id char(36) NOT NULL,
FirstName longtext NULL,
MiddleName longtext NULL,
LastName longtext NULL,
SchoolId char(36) NOT NULL,
FunctionRoleId char(36) NOT NULL,
Status int NOT NULL,
CONSTRAINT PK_Employee PRIMARY KEY (Id),
CONSTRAINT FK_Employee_FunctionRoles_FunctionRoleId FOREIGN KEY (FunctionRoleId) REFERENCES FunctionRoles (Id) ON DELETE CASCADE,
CONSTRAINT FK_Employee_Schools_SchoolId FOREIGN KEY (SchoolId) REFERENCES Schools (Id) ON DELETE CASCADE
);
info: Microsoft.EntityFrameworkCore.Database.Command[20101]
Executed DbCommand (5ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
INSERT INTO Schools (Id, Name, Status)
VALUES ('67fbf467-e8bb-40d8-bdea-c3a965204d77', 'EF Core School', 0);
info: Microsoft.EntityFrameworkCore.Database.Command[20101]
Executed DbCommand (4ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
INSERT INTO FunctionRoles (Id, Description, Name, SchoolId, Status)
VALUES ('f0fb348d-0905-4895-86b8-dcb9e0672f21', NULL, 'Manager', '67fbf467-e8bb-40d8-bdea-c3a965204d77', 0);
info: Microsoft.EntityFrameworkCore.Database.Command[20101]
Executed DbCommand (5ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
INSERT INTO Employee (Id, FirstName, FunctionRoleId, LastName, MiddleName, SchoolId, Status)
VALUES ('ce710a03-de94-4b58-97a9-3030ddfe2616', 'John', 'f0fb348d-0905-4895-86b8-dcb9e0672f21', 'Doe', NULL, '67fbf467-e8bb-40d8-bdea-c3a965204d77', 0);
info: Microsoft.EntityFrameworkCore.Database.Command[20101]
Executed DbCommand (28ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
CREATE INDEX IX_Employee_FunctionRoleId ON Employee (FunctionRoleId);
info: Microsoft.EntityFrameworkCore.Database.Command[20101]
Executed DbCommand (47ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
CREATE INDEX IX_Employee_SchoolId ON Employee (SchoolId);
info: Microsoft.EntityFrameworkCore.Database.Command[20101]
Executed DbCommand (27ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
CREATE INDEX IX_FunctionRoles_SchoolId ON FunctionRoles (SchoolId);
warn: Microsoft.EntityFrameworkCore.Query[10102]
The query uses a row limiting operator ('Skip'/'Take') without an 'OrderBy' operator. This may lead to unpredictable results.
info: Microsoft.EntityFrameworkCore.Database.Command[20101]
Executed DbCommand (16ms) [Parameters=[@__schoolId_0='67fbf467-e8bb-40d8-bdea-c3a965204d77'], CommandType='Text', CommandTimeout='30']
SELECT t1.Id, t1.Name, t1.Id0, t2.Id, t2.Name
FROM (
SELECT s.Id, s.Name, t0.Id AS Id0
FROM Schools AS s
LEFT JOIN (
SELECT t.Id, t.SchoolId
FROM (
SELECT f.Id, f.SchoolId, ROW_NUMBER() OVER(PARTITION BY f.SchoolId ORDER BY f.Id) AS row
FROM FunctionRoles AS f
WHERE f.Name = 'Manager'
) AS t
WHERE t.row <= 1
) AS t0 ON s.Id = t0.SchoolId
WHERE s.Id = @__schoolId_0
LIMIT 1
) AS t1
LEFT JOIN (
SELECT e.Id, CONCAT(CONCAT(CONCAT(COALESCE(e.FirstName, ''), COALESCE(CASE
WHEN e.MiddleName IS NULL OR (e.MiddleName = '') THEN NULL
ELSE CONCAT(' ', COALESCE(e.MiddleName, ''))
END, '')), ' '), COALESCE(e.LastName, '')) AS Name, e.SchoolId, e.FunctionRoleId
FROM Employee AS e
WHERE e.Status <> 1
) AS t2 ON (t1.Id = t2.SchoolId) AND (t1.Id0 = t2.FunctionRoleId)
ORDER BY t1.Id, t1.Id0, t2.Id
```
As you can see, the output also contains the SQL SELECT `f`.`Id`, `f`.`SchoolId`, ROW_NUMBER() OVER(PARTITION BY `f`.`SchoolId` ORDER BY `f`.`Id`) AS `row`, that does run as expected.
@FaizanMubasher How do you initialize your context (e.g. the UseMySql() call)?
As a side note, if you tag your Pomelo related questions on StackOverflow with pomelo-entityframeworkcore-mysql, we get notified and also answer them over there.
lauxjpn
on 12 Jan 2021
I think I am not properly using UseMySql() call. What I am doing is
optionsBuilder.UseMySql(_connectionString, new MySqlServerVersion(new Version()));
I am not doing this part serverVersion.AutoDetect(connectionString),
options => options.CharSetBehavior(CharSetBehavior.NeverAppend).
Does this impact on query translation/evaluation?
FaizanMubasher
on 12 Jan 2021
Does this impact on query translation/evaluation?
@FaizanMubasher If you don't correctly specify the version you are using, e.g. by using new MySqlServerVersion(new Version(8, 0, 22)) or ServerVersion.AutoDetect(connectionString), then you will not get all available features for the database version and type you are using. If you specify an empty Version object, as you seem to be doing, than you will get only the very basic features that MySQL supports. Window functions are not part of that, because they got only introduced recently in MySQL 8.0.0.
So to fix your issue, just make sure to specify the correct database version and type.
lauxjpn
on 12 Jan 2021
Message
An exception has been raised that is likely due to a transient failure. Consider enabling transient error resiliency by adding 'EnableRetryOnFailure()' to the 'UseMySql' call.
Inner Exception
The Command Timeout expired before the operation completed.
Stack Trace
at Pomelo.EntityFrameworkCore.MySql.Storage.Internal.MySqlExecutionStrategy.Execute[TState,TResult](TState state, Func`3 operation, Func`3 verifySucceeded)
at Microsoft.EntityFrameworkCore.Query.Internal.SingleQueryingEnumerable`1.Enumerator.MoveNext()
at System.Linq.Enumerable.SingleOrDefault[TSource](IEnumerable`1 source)
at Microsoft.EntityFrameworkCore.Query.Internal.QueryCompiler.Execute[TResult](Expression query)
at Microsoft.EntityFrameworkCore.Query.Internal.EntityQueryProvider.Execute[TResult](Expression expression)
at System.Linq.Queryable.FirstOrDefault[TSource](IQueryable`1 source)
at MBM.OnlineSms.Api.BusinessLayer.School.SchoolService.GetSchool(Guid schoolId) in D:\VSWorkspace\sms\src\Api\MBM.OnlineSms.Api.BusinessLayer\School\SchoolService.cs:line 375
at MBM.OnlineSms.Api.Controllers.SchoolController.GetSchoolDetails(Guid schoolId) in D:\VSWorkspace\sms\src\Api\MBM.OnlineSms.Api\Controllers\SchoolController.cs:line 88
at Microsoft.AspNetCore.Mvc.Infrastructure.ActionMethodExecutor.SyncActionResultExecutor.Execute(IActionResultTypeMapper mapper, ObjectMethodExecutor executor, Object controller, Object[] arguments)
at Microsoft.AspNetCore.Mvc.Infrastructure.ControllerActionInvoker.InvokeActionMethodAsync()
at Microsoft.AspNetCore.Mvc.Infrastructure.ControllerActionInvoker.Next(State& next, Scope& scope, Object& state, Boolean& isCompleted)
at Microsoft.AspNetCore.Mvc.Infrastructure.ControllerActionInvoker.InvokeNextActionFilterAsync()
Is that the full stack trace, do you have a MySqlException? It's possible that your connection string isn't set correctly.
mguinness
on 12 Jan 2021
This is the stack trace as shown by VS2019. By the way if I comment this line
Signers = s.Employees
.Where(e => e.Status != PersistentStatusEnum.Removed
&& e.FunctionRoleId == r.Id)
.Select(a => new NameValueType
{
Id = a.Id,
Name = a.FirstName + " " + a.MiddleName + " " + a.LastName
}).ToList(),
Everything works fine. I can't understand what's wrong in this LOCs.
FaizanMubasher
on 12 Jan 2021
Using ToQueryString() what is the SQL generated and what happens when you run it directly in MySQL Workbench?
mguinness
on 12 Jan 2021
Everything works fine. I can't understand what's wrong in this LOCs.
The SQL query you are generating is complex. Since EF Core 3.0, a single query is generated in contrast to EF Core versions prior to that, which can lead to a cartesian explosion.
So if your database contains more than just a few rows for the entities you are trying to query again, than this can lead to millions or even more rows that need to be evaluated by the database (or even returned).
The recommended way to solve those complex queries before EF Core 5, was to manually split the query up into multiple queries. You then execute these queries one after another, and then do the final transformations (e.g. DTO generation) with LINQ-to-Objects (client-side).
Since EF Core 5.0 however, Split Queries and Filtered Includes are a thing.
Therefore, making sure that you are using .Include() calls, filter them as required, and then just adding .AsSplitQuery() to your query, is likely to solve your issue.
Using the example code I posted before, using Include(), filtered includes and AsSplitQuery() could be used in the following way:
```c#
var schoolId = new Guid("67FBF467-E8BB-40D8-BDEA-C3A965204D77"); // EF Core School
// Compose and execute the query.
var school = dbContext
.Set
.Include(s => s.Employees.Where(e => e.Status != PersistentStatusEnum.Removed &&
e.FunctionRole.Name == FunctionRolesEnum.Manager.ToString()))
.AsSplitQuery()
.FirstOrDefault(s => s.Id == schoolId);
// Compose your DTO.
var schoolDto = school != null
? new SchoolSummaryDto
{
Signers = school.Employees
.Select(
a => new NameValueType
{
Id = a.Id,
Name = a.FirstName + (string.IsNullOrEmpty(a.MiddleName) ? null : " " + a.MiddleName) + " " + a.LastName
})
.ToList(),
SchoolId = school.Id,
SchoolName = school.Name,
}
: null;
The same result can be achieved by splitting the query manually:
```c#
var schoolId = new Guid("67FBF467-E8BB-40D8-BDEA-C3A965204D77"); // EF Core School
// Manually split the query into parts and run them individually.
var school = dbContext.Set<School>().FirstOrDefault(s => s.Id == schoolId);
dbContext.Entry(school).Collection(s => s.Employees).Query()
.Where(e => e.Status != PersistentStatusEnum.Removed &&
e.FunctionRole.Name == FunctionRolesEnum.Manager.ToString())
.Load();
// Compose your DTO.
var schoolDto = school != null
? new SchoolSummaryDto
{
Signers = school.Employees
.Select(
a => new NameValueType
{
Id = a.Id,
Name = a.FirstName + (string.IsNullOrEmpty(a.MiddleName) ? null : " " + a.MiddleName) + " " + a.LastName
})
.ToList(),
SchoolId = school.Id,
SchoolName = school.Name,
}
: null;
Also see Avoid cartesian explosion when loading related entities.
lauxjpn
on 12 Jan 2021
Resolved after specifying correct version.
FaizanMubasher
on 19 Jan 2021
Related issues
IonRobu
路
3Comments
hainguyenthanh
路
3Comments
leobert1226
路
3Comments
guaicaiboshi
路
4Comments
cetubig
路
3Comments