title: EF Core: Group By Failure - Translation of 'Select' which contains grouping parameter without composition is not supported
body: <p>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.</p>
  <pre><code>private IQueryable&lt;CommentResponseData&gt; LatestCommentResponses()
          {
  
              var commentResponses = from responses in Repository.CommentResponses
                                     group responses by responses.CommentId into responseGroup
                                     let latestComment = responseGroup.OrderByDescending(a =&gt; 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;
          }
  </code></pre>
  <ul>
  <li>SQL generated in EF6:</li>
  </ul>
  <pre><code>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]
  
  </code></pre>
  <ul>
  <li>Exception in EFCore 6:</li>
  </ul>
  <pre><code>The LINQ expression 'DbSet&lt;ECommentResponse&gt;()
      .GroupBy(responses =&gt; responses.CommentId)
      .Select(responseGroup =&gt; new { 
          responseGroup = responseGroup, 
          latestComment = responseGroup
              .AsQueryable()
              .OrderByDescending(a =&gt; 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.
  </code></pre>
  <ul>
  <li>Expression Tree in EFCore 6</li>
  </ul>
  <pre><code>.Call System.Linq.Queryable.Join(
      .Call System.Linq.Queryable.Select(
          .Call System.Linq.Queryable.GroupBy(
              .Call Microsoft.EntityFrameworkCore.EntityFrameworkQueryableExtensions.AsNoTracking(.Extension&lt;Microsoft.EntityFrameworkCore.Query.QueryRootExpression&gt;)
              ,
              '(.Lambda #Lambda1&lt;System.Func`2[Lw.Domain.ICommentResponse,System.Nullable`1[System.Int64]]&gt;)),
          '(.Lambda #Lambda2&lt;System.Func`2[System.Linq.IGrouping`2[System.Nullable`1[System.Int64],Lw.Domain.ICommentResponse],&lt;&gt;f__AnonymousType183`2[System.Linq.IGrouping`2[System.Nullable`1[System.Int64],Lw.Domain.ICommentResponse],Lw.Domain.ICommentResponse]]&gt;))
      ,
      .Call Microsoft.EntityFrameworkCore.EntityFrameworkQueryableExtensions.AsNoTracking(.Extension&lt;Microsoft.EntityFrameworkCore.Query.QueryRootExpression&gt;)
      ,
      '(.Lambda #Lambda3&lt;System.Func`2[&lt;&gt;f__AnonymousType183`2[System.Linq.IGrouping`2[System.Nullable`1[System.Int64],Lw.Domain.ICommentResponse],Lw.Domain.ICommentResponse],System.Nullable`1[System.Int64]]&gt;),
      '(.Lambda #Lambda4&lt;System.Func`2[Lw.Domain.IUser,System.Nullable`1[System.Int64]]&gt;),
      '(.Lambda #Lambda5&lt;System.Func`3[&lt;&gt;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]&gt;))
  
  .Lambda #Lambda1&lt;System.Func`2[Lw.Domain.ICommentResponse,System.Nullable`1[System.Int64]]&gt;(Lw.Domain.ICommentResponse $responses)
  {
      $responses.CommentId
  }
  
  .Lambda #Lambda2&lt;System.Func`2[System.Linq.IGrouping`2[System.Nullable`1[System.Int64],Lw.Domain.ICommentResponse],&lt;&gt;f__AnonymousType183`2[System.Linq.IGrouping`2[System.Nullable`1[System.Int64],Lw.Domain.ICommentResponse],Lw.Domain.ICommentResponse]]&gt;(System.Linq.IGrouping`2[System.Nullable`1[System.Int64],Lw.Domain.ICommentResponse] $responseGroup)
  {
      .New &lt;&gt;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&lt;System.Func`2[Lw.Domain.ICommentResponse,System.Nullable`1[System.DateTimeOffset]]&gt;)))
  }
  
  .Lambda #Lambda3&lt;System.Func`2[&lt;&gt;f__AnonymousType183`2[System.Linq.IGrouping`2[System.Nullable`1[System.Int64],Lw.Domain.ICommentResponse],Lw.Domain.ICommentResponse],System.Nullable`1[System.Int64]]&gt;(&lt;&gt;f__AnonymousType183`2[System.Linq.IGrouping`2[System.Nullable`1[System.Int64],Lw.Domain.ICommentResponse],Lw.Domain.ICommentResponse] $&lt;&gt;h__TransparentIdentifier0)
  {
      ($&lt;&gt;h__TransparentIdentifier0.latestComment).UserId
  }
  
  .Lambda #Lambda4&lt;System.Func`2[Lw.Domain.IUser,System.Nullable`1[System.Int64]]&gt;(Lw.Domain.IUser $user) {
      (System.Nullable`1[System.Int64])$user.Id
  }
  
  .Lambda #Lambda5&lt;System.Func`3[&lt;&gt;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]&gt;(
      &lt;&gt;f__AnonymousType183`2[System.Linq.IGrouping`2[System.Nullable`1[System.Int64],Lw.Domain.ICommentResponse],Lw.Domain.ICommentResponse] $&lt;&gt;h__TransparentIdentifier0,
      Lw.Domain.IUser $user) {
      .New Lw.Domain.Base.Extension.Selectors.CommentQueryables+CommentResponseData(){
          CommentId = ($&lt;&gt;h__TransparentIdentifier0.responseGroup).Key,
          LastResponseTime = ($&lt;&gt;h__TransparentIdentifier0.latestComment).OriginalCreatedTime,
          ResponseCount = .Call System.Linq.Enumerable.Count($&lt;&gt;h__TransparentIdentifier0.responseGroup),
          LastResponseBy = $user.FullName,
          LastResponseMessage = ($&lt;&gt;h__TransparentIdentifier0.latestComment).Body
      }
  }
  
  .Lambda #Lambda6&lt;System.Func`2[Lw.Domain.ICommentResponse,System.Nullable`1[System.DateTimeOffset]]&gt;(Lw.Domain.ICommentResponse $a)
  {
      $a.OriginalCreatedTime
  }
  
  </code></pre>
  <p>NOTE:</p>
  <ol>
  <li>No custom Expression Visitor has been implemented</li>
  <li>If the above query can be fully converted into SQL in EF6, then why can't it be the case in EFCore 6 world</li>
  <li>Is this an existing bug in EFCore6</li>
  </ol>
  <p>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</p>