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

Trematode

Answer 1

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

Hawkins

Thank you for the suggestion. The issue as usual is that we can't afford to make any amends to our Linq queries explicitly and would like it to run as it (if by manipulating the expression tree is the only and last resort). Having said that, why would this not get converted to SQL explicitly in EFCore, you think it's a bug? – Trematode

Actually your query is very complex for translation and EF Core 6 just introduced such translation with all uncovered bugs. My variant is analogue how it has to be translated internally. And... it is not faster way as usual with EF limitations. – Hawkins

Agree with your points, but as of now we cannot make changes to these kind of linq queries. – Trematode

Transforming query to be translatable is complex task as translation itself. Don‘t waste time and resources, create EF Core bug, wait for fix or rewrite your queries. – Hawkins

Make sense, I have raised it as a bug on EF Core, please upvote if you can: github.com/dotnet/efcore/issues/27426 – Trematode

No problem, upvoted. – Hawkins

As of 2024/07/01 this bug is not fixed and seems to be closed in Github – Kindness

Answer 2

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.

Hoye