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

Question

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:

1. No custom Expression Visitor has been implemented
2. If the above query can be fully converted into SQL in EF6, then why can't it be the case in EFCore 6 world
3. 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

Answer 1

Workaround on 2023-08-23

Using EF Core 7, I still have this issue. I found out that It can be fixed by avoiding the let statement.
Here is a pair of sample queries to illustrate the change needed:

buggy query:

// This query won't translate
var query1 = 
    from record in source
    orderby record.time descending
    group record by new { record.Location } into recordGroup
    let s = recordGroup.Sum(...)
    let c = recordGroup.Count()
    select new dto
    {
        Location = recordGroup.Key.Location,
        summary = new summaryDto
        {
            sum = s,
            count = c,
        }
    };

working query:

// This query works just fine:
var query1 = 
    from record in source
    orderby record.time descending
    group record by new { record.Location } into recordGroup
    select new dto
    {
        Location = recordGroup.Key.Location,
        summary = new summaryDto
        {
            sum = recordGroup.Sum(...),
            count = recordGroup.Count(),
        }
    };

As you can see, they differ in that the first one is using let and the second one avoids that.

Answer 2

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