linq - EF 核心 : Group By Failure - Translation of 'Select' which contains grouping parameter without composition is not supported

Question

下面的 LINQ 查询在 EF6 世界中工作得很好，整个查询似乎在服务器上进行评估(使用 SQL Profiler 检查)但在 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;
        }

• 在 EF6 中生成的 SQL:

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]

• 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.

• 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
}

注意:

1. 没有实现自定义表达式访问器
2. 如果上面的查询在 EF6 中可以完全转换成 SQL，那为什么在 EFCore 6 的世界里就不能呢
3. 这是 EFCore6 中的现有错误吗

EF 核心版本:6.0.1数据库提供者:Microsoft.EntityFrameworkCore.SqlServer目标框架:.NET 6.0操作系统:Win 10 专业版IDE:Visual Studio 2022 v17.0.4

Answer 1

考虑重写您的查询，直到修复此错误

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,
    };