The LINQ expression could not be translated. #20175
Comments
|
@cosmin-ciuc Since 2.2 was translating it to multiple queries, it means that part of the query was evaluated on the client. In 3.1 client evaluation is no longer happening (see #12795 for details). Can you provide a full repro (entities, dbcontext etc) so we can see if there is a workaround for your particular case. |
|
@maumar thank you for answering. As for providing a repository which could replicate the issue that won't happen very soon because it takes time to strip down a bigger Web API solution constructed on UnitOfWork+Repository pattern to isolate this case. I'll try to do it in my free time. |
|
Before starting to strip away my solution to isolate this particular case I would like to ask if the following use case is supported in EF Core 3.1. Author
Book
Edition
Let's say that we have stored all the books that have been published in US in the past 50 years. I want to retrieve a paginated list of books with a page size of 10. For each book, I want to know its author and whether the author is still living or is dead. For each book, I want to know the name of the publishing house that published the latest hardcover edition and the name of the publishing house that published the first edition of the book. And I want to order this list by the name of the publishing house that published the latest hardcover edition. And maybe I want to filter the list to only the books that were first published by a particular publishing house, or those books that were first published in the year 1978 for example. Will I be able to write such a LINQ query using EF Core 3.1 that will be executed server-side? I don't want to retrieve in memory any of these tables because they are huge and I want to read only 10 records at a time. The ORDER BY requirement is mandatory and the ordering has to be done server-side for the pagination to properly work. I guess that what I'm trying to say is that maybe the decision to prohibit the translation of a LINQ query into several SQL statements might not have been a wise one. Maybe we are lucky that we have in our solution just a limited number of complex queries that maybe could be somehow refactored to work correctly in EF Core 3.1. But what if there are in the world big mission-critical solutions, with many complex queries, in which such a refactoring may not be feasible or even possible. What will happen to these solutions? Will they be frozen to EF Core 2.2 because that is the version of EF Core that allows the server-side execution of the complex queries through several SQL statements? |
|
I came up with something like this, it may not be exactly what you were looking for, but close enough. class Program
{
static void Main(string[] args)
{
using var ctx = new MyContext();
ctx.Database.EnsureDeleted();
ctx.Database.EnsureCreated();
var page = 1;
var query = ctx.Books
.Where(b => b.Editions.OrderBy(e => e.PublishingDate).Select(e => e.PublishingHouseName).FirstOrDefault() == "Penguin Random House")
.Where(b => b.Editions.Any(e => e.PublishingDate.Year == 1978))
.OrderBy(b => b.Editions.Where(e => e.CoverType == "Hard").OrderByDescending(e => e.PublishingDate).Select(e => e.PublishingHouseName).FirstOrDefault())
.Skip((page - 1) * 10).Take(10)
.Select(b => new
{
Book = b,
AuthorName = b.Author.Name,
AuthorAlive = b.Author.DateOfDeath == null,
LatestHardCover = b.Editions.Where(e => e.CoverType == "Hard").OrderByDescending(e => e.PublishingDate).Select(e => e.PublishingHouseName).FirstOrDefault(),
FirstEdition = b.Editions.OrderBy(e => e.PublishingDate).Select(e => e.PublishingHouseName).FirstOrDefault(),
});
var result = query.ToList();
}
}
public class Author
{
public Guid Id { get; set; }
public string Name { get; set; }
public DateTime BirthDate { get; set; }
public DateTime? DateOfDeath { get; set; }
public List<Book> Books { get; set; }
}
public class Book
{
public Guid Id { get; set; }
public string Title { get; set; }
public string Genre { get; set; }
public DateTime DateOfWriting { get; set; }
public Guid AuthorId { get; set; }
public Author Author { get; set; }
public List<Edition> Editions { get; set; }
}
public class Edition
{
public Guid Id { get; set; }
public DateTime PublishingDate { get; set; }
public string CoverType { get; set; }
public string PublishingHouseName { get; set; }
public Book Book { get; set; }
public Guid BookId { get; set; }
}
public class MyContext : DbContext
{
public DbSet<Author> Authors { get; set; }
public DbSet<Book> Books { get; set; }
public DbSet<Edition> Editions { get; set; }
protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
{
optionsBuilder.UseSqlServer(@"Server=(localdb)\mssqllocaldb;Database=Repro20175;Trusted_Connection=True;MultipleActiveResultSets=true");
}
}It all gets translated into one sql statement like so: SELECT [t].[Id], [t].[AuthorId], [t].[DateOfWriting], [t].[Genre], [t].[Title], [a].[Name] AS [AuthorName], CASE
WHEN [a].[DateOfDeath] IS NULL THEN CAST(1 AS bit)
ELSE CAST(0 AS bit)
END AS [AuthorAlive], (
SELECT TOP(1) [e].[PublishingHouseName]
FROM [Editions] AS [e]
WHERE ([t].[Id] = [e].[BookId]) AND ([e].[CoverType] = N'Hard')
ORDER BY [e].[PublishingDate] DESC) AS [LatestHardCover], (
SELECT TOP(1) [e0].[PublishingHouseName]
FROM [Editions] AS [e0]
WHERE [t].[Id] = [e0].[BookId]
ORDER BY [e0].[PublishingDate]) AS [FirstEdition]
FROM (
SELECT [b].[Id], [b].[AuthorId], [b].[DateOfWriting], [b].[Genre], [b].[Title], (
SELECT TOP(1) [e1].[PublishingHouseName]
FROM [Editions] AS [e1]
WHERE ([b].[Id] = [e1].[BookId]) AND ([e1].[CoverType] = N'Hard')
ORDER BY [e1].[PublishingDate] DESC) AS [c]
FROM [Books] AS [b]
WHERE ((
SELECT TOP(1) [e2].[PublishingHouseName]
FROM [Editions] AS [e2]
WHERE [b].[Id] = [e2].[BookId]
ORDER BY [e2].[PublishingDate]) = N'Penguin Random House') AND EXISTS (
SELECT 1
FROM [Editions] AS [e3]
WHERE ([b].[Id] = [e3].[BookId]) AND (DATEPART(year, [e3].[PublishingDate]) = 1978))
ORDER BY (
SELECT TOP(1) [e1].[PublishingHouseName]
FROM [Editions] AS [e1]
WHERE ([b].[Id] = [e1].[BookId]) AND ([e1].[CoverType] = N'Hard')
ORDER BY [e1].[PublishingDate] DESC)
OFFSET @__p_0 ROWS FETCH NEXT @__p_1 ROWS ONLY
) AS [t]
INNER JOIN [Authors] AS [a] ON [t].[AuthorId] = [a].[Id]
ORDER BY [t].[c]wrt not allowing client translation - here is (somewhat long) discussion on why we made the decision #12795 TL;DR Also, client eval was a huge problem for us when adding new features - stuff that didn't translate would client eval and seem to work. We then would improve the translation but introduced some bugs in the process. People would experience that as regressions, which was not a great experience. There are also other, more technical reasons for issuing just one query, e.g. async is easier and we had issues with data consistency when database would get modified between first and second query was executed. |
|
Thank you @maumar for taking the time to write this very detailed response. Following your example I'll try in the following days to do the same for our particular case. |
|
I'm sorry I've been misleading. The issue can be replicated with Microsoft.EntityFrameworkCore.SQLite 3.1.2 not with Microsoft.EntityFrameworkCore.SqlServer 3.1.2. |
ajcvickers
added
closed-could-not-reproduce
customer-reported
and removed
type-bug
labels
I have a perfectly functional query which is executed against a Microsoft SQL Server Database. I have optimized the query for EF Core 2.2 to be executed server-side not client-side. The same query in EF Core 3.1 produces the exception:
I do not understand why EF Core 2.2 was able to translate the query and EF Core 3.1 is not.
Steps to reproduce
The query is build like this:
The TransferPropertiesToDocumentEntity method is this one:
With EF Core 2.2 this query gets translated into several SQL statements:
Further technical details
EF Core version: 3.1.2
Database provider: Microsoft.EntityFrameworkCore.SqlServer
Target framework: .NET Core 3.1
Operating system: Windows 10 x64
IDE: Visual Studio 2019 16.4.3
The text was updated successfully, but these errors were encountered: