The below LINQ query works perfectly fine in EF6 world where the whole query seems to be evaluated at the server (checked with SQL Profiler) but fails in EFCore6.
private IQueryable<CommentResponseData> LatestCommentResponses()
{
var commentResponses = from responses in Repository.CommentResponses
group responses by responses.CommentId into responseGroup
let latestComment = responseGroup.OrderByDescending(a => a.OriginalCreatedTime).FirstOrDefault()
join user in Repository.Users on latestComment.UserId equals user.Id
select new CommentResponseData
{
CommentId = responseGroup.Key,
LastResponseTime = latestComment.OriginalCreatedTime,
ResponseCount = responseGroup.Count(),
LastResponseBy = user.FullName,
LastResponseMessage = latestComment.Body,
};
return commentResponses;
}
- SQL generated in EF6:
SELECT
1 AS [C1],
[Project3].[CommentId] AS [CommentId],
[Project3].[OriginalCreatedTime] AS [OriginalCreatedTime],
[Project3].[C1] AS [C2],
[Project3].[FullName] AS [FullName],
[Project3].[Body] AS [Body]
FROM ( SELECT
[Distinct1].[CommentId] AS [CommentId],
[Extent3].[FullName] AS [FullName],
[Limit1].[Body] AS [Body],
[Limit1].[OriginalCreatedTime] AS [OriginalCreatedTime],
(SELECT
COUNT(1) AS [A1]
FROM [dbo].[CommentResponses] AS [Extent4]
WHERE [Distinct1].[CommentId] = [Extent4].[CommentId]) AS [C1]
FROM (SELECT DISTINCT
[Extent1].[CommentId] AS [CommentId]
FROM [dbo].[CommentResponses] AS [Extent1] ) AS [Distinct1]
OUTER APPLY (SELECT TOP (1) [Project2].[Body] AS [Body], [Project2].[OriginalCreatedTime] AS [OriginalCreatedTime], [Project2].[UserId] AS [UserId]
FROM ( SELECT
[Extent2].[Body] AS [Body],
[Extent2].[OriginalCreatedTime] AS [OriginalCreatedTime],
[Extent2].[UserId] AS [UserId]
FROM [dbo].[CommentResponses] AS [Extent2]
WHERE [Distinct1].[CommentId] = [Extent2].[CommentId]
) AS [Project2]
ORDER BY [Project2].[OriginalCreatedTime] DESC ) AS [Limit1]
INNER JOIN [dbo].[Users] AS [Extent3] ON [Limit1].[UserId] = [Extent3].[Id]
) AS [Project3]
- Exception in EFCore 6:
The LINQ expression 'DbSet<ECommentResponse>()
.GroupBy(responses => responses.CommentId)
.Select(responseGroup => new {
responseGroup = responseGroup,
latestComment = responseGroup
.AsQueryable()
.OrderByDescending(a => a.OriginalCreatedTime)
.FirstOrDefault()
})' could not be translated. Additional information: Translation of 'Select' which contains grouping parameter without composition is not supported. 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.
- Expression Tree in EFCore 6
.Call System.Linq.Queryable.Join(
.Call System.Linq.Queryable.Select(
.Call System.Linq.Queryable.GroupBy(
.Call Microsoft.EntityFrameworkCore.EntityFrameworkQueryableExtensions.AsNoTracking(.Extension<Microsoft.EntityFrameworkCore.Query.QueryRootExpression>)
,
'(.Lambda #Lambda1<System.Func`2[Lw.Domain.ICommentResponse,System.Nullable`1[System.Int64]]>)),
'(.Lambda #Lambda2<System.Func`2[System.Linq.IGrouping`2[System.Nullable`1[System.Int64],Lw.Domain.ICommentResponse],<>f__AnonymousType183`2[System.Linq.IGrouping`2[System.Nullable`1[System.Int64],Lw.Domain.ICommentResponse],Lw.Domain.ICommentResponse]]>))
,
.Call Microsoft.EntityFrameworkCore.EntityFrameworkQueryableExtensions.AsNoTracking(.Extension<Microsoft.EntityFrameworkCore.Query.QueryRootExpression>)
,
'(.Lambda #Lambda3<System.Func`2[<>f__AnonymousType183`2[System.Linq.IGrouping`2[System.Nullable`1[System.Int64],Lw.Domain.ICommentResponse],Lw.Domain.ICommentResponse],System.Nullable`1[System.Int64]]>),
'(.Lambda #Lambda4<System.Func`2[Lw.Domain.IUser,System.Nullable`1[System.Int64]]>),
'(.Lambda #Lambda5<System.Func`3[<>f__AnonymousType183`2[System.Linq.IGrouping`2[System.Nullable`1[System.Int64],Lw.Domain.ICommentResponse],Lw.Domain.ICommentResponse],Lw.Domain.IUser,Lw.Domain.Base.Extension.Selectors.CommentQueryables CommentResponseData]>))
.Lambda #Lambda1<System.Func`2[Lw.Domain.ICommentResponse,System.Nullable`1[System.Int64]]>(Lw.Domain.ICommentResponse $responses)
{
$responses.CommentId
}
.Lambda #Lambda2<System.Func`2[System.Linq.IGrouping`2[System.Nullable`1[System.Int64],Lw.Domain.ICommentResponse],<>f__AnonymousType183`2[System.Linq.IGrouping`2[System.Nullable`1[System.Int64],Lw.Domain.ICommentResponse],Lw.Domain.ICommentResponse]]>(System.Linq.IGrouping`2[System.Nullable`1[System.Int64],Lw.Domain.ICommentResponse] $responseGroup)
{
.New <>f__AnonymousType183`2[System.Linq.IGrouping`2[System.Nullable`1[System.Int64],Lw.Domain.ICommentResponse],Lw.Domain.ICommentResponse](
$responseGroup,
.Call System.Linq.Enumerable.FirstOrDefault(.Call System.Linq.Enumerable.OrderByDescending(
$responseGroup,
.Lambda #Lambda6<System.Func`2[Lw.Domain.ICommentResponse,System.Nullable`1[System.DateTimeOffset]]>)))
}
.Lambda #Lambda3<System.Func`2[<>f__AnonymousType183`2[System.Linq.IGrouping`2[System.Nullable`1[System.Int64],Lw.Domain.ICommentResponse],Lw.Domain.ICommentResponse],System.Nullable`1[System.Int64]]>(<>f__AnonymousType183`2[System.Linq.IGrouping`2[System.Nullable`1[System.Int64],Lw.Domain.ICommentResponse],Lw.Domain.ICommentResponse] $<>h__TransparentIdentifier0)
{
($<>h__TransparentIdentifier0.latestComment).UserId
}
.Lambda #Lambda4<System.Func`2[Lw.Domain.IUser,System.Nullable`1[System.Int64]]>(Lw.Domain.IUser $user) {
(System.Nullable`1[System.Int64])$user.Id
}
.Lambda #Lambda5<System.Func`3[<>f__AnonymousType183`2[System.Linq.IGrouping`2[System.Nullable`1[System.Int64],Lw.Domain.ICommentResponse],Lw.Domain.ICommentResponse],Lw.Domain.IUser,Lw.Domain.Base.Extension.Selectors.CommentQueryables CommentResponseData]>(
<>f__AnonymousType183`2[System.Linq.IGrouping`2[System.Nullable`1[System.Int64],Lw.Domain.ICommentResponse],Lw.Domain.ICommentResponse] $<>h__TransparentIdentifier0,
Lw.Domain.IUser $user) {
.New Lw.Domain.Base.Extension.Selectors.CommentQueryables CommentResponseData(){
CommentId = ($<>h__TransparentIdentifier0.responseGroup).Key,
LastResponseTime = ($<>h__TransparentIdentifier0.latestComment).OriginalCreatedTime,
ResponseCount = .Call System.Linq.Enumerable.Count($<>h__TransparentIdentifier0.responseGroup),
LastResponseBy = $user.FullName,
LastResponseMessage = ($<>h__TransparentIdentifier0.latestComment).Body
}
}
.Lambda #Lambda6<System.Func`2[Lw.Domain.ICommentResponse,System.Nullable`1[System.DateTimeOffset]]>(Lw.Domain.ICommentResponse $a)
{
$a.OriginalCreatedTime
}
NOTE:
- No custom Expression Visitor has been implemented
- If the above query can be fully converted into SQL in EF6, then why can't it be the case in EFCore 6 world
- Is this an existing bug in EFCore6
EF Core version: 6.0.1 Database provider: Microsoft.EntityFrameworkCore.SqlServer Target framework: .NET 6.0 Operating system: Win 10 Pro IDE: Visual Studio 2022 v17.0.4
CodePudding user response:
Consider to rewrite your query until this bug is fixed
var sourceQery = Repository.CommentResponses;
var groupingQuery =
from responses in sourceQery
group responses by responses.CommentId into responseGroup
select new
{
CommentId = responseGroup.Key,
ResponseCount = responseGroup.Count()
};
var commentResponses =
from g in groupingQuery
from latestComment in sourceQery
.Where(l => l.CommentId == g.CommentId)
.OrderByDescending(a => a.OriginalCreatedTime)
.Take(1)
join user in Repository.Users on latestComment.UserId equals user.Id
select new CommentResponseData
{
CommentId = g.CommentId,
LastResponseTime = latestComment.OriginalCreatedTime,
ResponseCount = g.ResponseCount,
LastResponseBy = user.FullName,
LastResponseMessage = latestComment.Body,
};