Group By Failure - Translation of 'Select' which contains grouping parameter without composition is not supported. #27426

ankitmatrix08 · 2022-02-10T16:12:51Z

Another set of Group By failure unfortunately: @smitpatel
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 that is being tracked, if so, by when can we expect it to be released
Using GroupBy with auto-generated expression passed to Select throws an exception in RC1 and RC2 #26525 - doesn't seem to help

P.S. I have gone through probably all of the queries raised on Group By in this forum and it feels very confusing as to which one works and which one doesn't in which versions. A summary of the compatibility report would be very handy for all of the EFCore customers

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

The text was updated successfully, but these errors were encountered:

AndriySvyryd · 2022-02-11T21:44:13Z

@smitpatel Please link to the duplicate issue

smitpatel · 2022-02-11T22:16:00Z

Duplicate of #19929

AndriySvyryd · 2022-02-12T03:14:40Z

We are planning to support almost all of the EF6 queries in EF Core 7.

ankitmatrix08 · 2022-02-13T17:59:38Z

@smitpatel @AndriySvyryd So essentially all of the issues mentioned under #19929 AND as you said almost all of the EF6 queries will be supported in EF Core 7.

I am asking this again as we have multiple scenarios of group by queries and we need a clear direction whether to go about upgrading to EF Core 6/7 from EF6 at all or not.

We can't change any existing LINQ queries written in EF6 which are by default server translatable, I do understand that if there are queries which are client evaluated then we need to have some kind of fix (preferably by implementing RelationalQueryTranslationPreprocessor)

ankitmatrix08 · 2022-02-14T12:06:26Z

@smitpatel @AndriySvyryd Could you re-open this, as I have found one more group by related issue which needs clarification at least.
Per the Release Note , translatation of GroupBy followed by FirstOrDefault (or similar) over a group is supported in EFCore 6, but then the below query is failing:

Query:

DocumentBusinessEntityConfigs.Where(e => e.IsActive)
			.GroupBy(x => x.Name)
			.Select(x => x.FirstOrDefault());

Expression Tree:

.Call System.Linq.Queryable.Select(
    .Call System.Linq.Queryable.GroupBy(
        .Call System.Linq.Queryable.Where(
            .Call Microsoft.EntityFrameworkCore.EntityFrameworkQueryableExtensions.AsNoTracking(.Extension<Microsoft.EntityFrameworkCore.Query.QueryRootExpression>)
            ,
            '(.Lambda #Lambda1<System.Func`2[Lw.Domain.IDocumentBusinessEntityConfig,System.Boolean]>)),
        '(.Lambda #Lambda2<System.Func`2[Lw.Domain.IDocumentBusinessEntityConfig,System.String]>)),
    '(.Lambda #Lambda3<System.Func`2[System.Linq.IGrouping`2[System.String,Lw.Domain.IDocumentBusinessEntityConfig],Lw.Domain.IDocumentBusinessEntityConfig]>))

.Lambda #Lambda1<System.Func`2[Lw.Domain.IDocumentBusinessEntityConfig,System.Boolean]>(Lw.Domain.IDocumentBusinessEntityConfig $e)
{
    $e.IsActive
}

.Lambda #Lambda2<System.Func`2[Lw.Domain.IDocumentBusinessEntityConfig,System.String]>(Lw.Domain.IDocumentBusinessEntityConfig $x)
{
    $x.Name
}

.Lambda #Lambda3<System.Func`2[System.Linq.IGrouping`2[System.String,Lw.Domain.IDocumentBusinessEntityConfig],Lw.Domain.IDocumentBusinessEntityConfig]>(System.Linq.IGrouping`2[System.String,Lw.Domain.IDocumentBusinessEntityConfig] $x)
{
    .Call System.Linq.Enumerable.FirstOrDefault($x)
}

Exception:

Nullable object must have a value.

   at System.ThrowHelper.ThrowInvalidOperationException_InvalidOperation_NoValue()
   at System.Nullable`1.get_Value()
   at Microsoft.EntityFrameworkCore.Query.SqlExpressions.SelectExpression.ClientProjectionRemappingExpressionVisitor.Visit(Expression expression)
   at System.Linq.Expressions.ExpressionVisitor.VisitUnary(UnaryExpression node)
   at System.Linq.Expressions.ExpressionVisitor.Visit(Expression node)
   at Microsoft.EntityFrameworkCore.Query.SqlExpressions.SelectExpression.ClientProjectionRemappingExpressionVisitor.Visit(Expression expression)
   at Microsoft.EntityFrameworkCore.Query.SqlExpressions.SelectExpression.ApplyProjection(Expression shaperExpression, ResultCardinality resultCardinality, QuerySplittingBehavior querySplittingBehavior)
   at Microsoft.EntityFrameworkCore.Query.Internal.SelectExpressionProjectionApplyingExpressionVisitor.VisitExtension(Expression extensionExpression)
   at System.Linq.Expressions.Expression.Accept(ExpressionVisitor visitor)
   at System.Linq.Expressions.ExpressionVisitor.Visit(Expression node)
   at Microsoft.EntityFrameworkCore.Query.RelationalQueryTranslationPostprocessor.Process(Expression query)
   at Microsoft.EntityFrameworkCore.Query.QueryCompilationContext.CreateQueryExecutor[TResult](Expression query)
   at Microsoft.EntityFrameworkCore.Storage.Database.CompileQuery[TResult](Expression query, Boolean async)
   at Microsoft.EntityFrameworkCore.Query.Internal.QueryCompiler.CompileQueryCore[TResult](IDatabase database, Expression query, IModel model, Boolean async)
   at Microsoft.EntityFrameworkCore.Query.Internal.QueryCompiler.<>c__DisplayClass9_0`1.<Execute>b__0()
   at Microsoft.EntityFrameworkCore.Query.Internal.CompiledQueryCache.GetOrAddQuery[TResult](Object cacheKey, Func`1 compiler)
   at Microsoft.EntityFrameworkCore.Query.Internal.QueryCompiler.Execute[TResult](Expression query)
   at Microsoft.EntityFrameworkCore.Query.Internal.EntityQueryProvider.Execute[TResult](Expression expression)
   at System.Linq.Queryable.Count[TSource](IQueryable`1 source)

smitpatel · 2022-02-14T19:18:20Z

Duplicate of #26748

ankitmatrix08 · 2022-02-14T19:35:29Z

@AndriySvyryd assuming "ef6-parity" issues will be fixed as part of EF Core 7?

AndriySvyryd · 2022-02-14T19:37:10Z

@ankitmatrix08 Some of them. See #24106 for our plan

ankitmatrix08 · 2022-02-14T19:40:09Z

We are planning to support almost all of the EF6 queries in EF Core 7.

This still remains true? I mean at least those queries which are getting translated to SQL in EF6 must work in EF Core 7.

AndriySvyryd · 2022-02-14T19:50:36Z

@ankitmatrix08 Most EF6 queries will be supported, though some might require to be client-evaluated. Due to the size of the domain we'll never be 100% sure that all EF6 queries work.

ajcvickers · 2022-02-14T19:52:29Z

@ankitmatrix08 We plan to support almost all queries. The exceptions will likely be for things like:

Very poor translations that are essentially a pit-of-failure
Cases where it is easy and preferable to write the query in some other way
Very obscure edge cases
Cases where the complexity of the change needed in EF Core mean they will have to be punted to a future release

Our goal is to make the transition from EF6 as smooth as possible, but there are always other factors that play into the best thing to do in each individual case.

DushyantSinghChouhan · 2024-09-20T14:13:29Z

Its still an issue in EF 8.0.8. Getting the same issue even after migrating. Below is my query

System.InvalidOperationException: The LINQ expression 'DbSet()
.SelectMany(
collectionSelector: x => DbSetTableB()
.Where(y => (int?)y.Id == x.MatchId)
.DefaultIfEmpty(),
resultSelector: (a, b) => new {
entity = a,
vendor = b
})
.Select(x => new QueueDataModel{
QueueId = x.entity.Id,
QueueType = x.entity.Type,
Name = x.entity.Name,
Status = x.entity.Status,
TINLast4 = ContextExtensions.JsonValue(
expression: x.entity.EntityJson,
path: "$.TINLast4"),
MetaData = ContextExtensions.JsonQuery(
expression: x.entity.EntityJson,
path: "$.MetaData"),
PersonFlag = ContextExtensions.JsonValue(
expression: x.entity.EntityJson,
path: "$.PersonFlag"),
ClientContact = ContextExtensions.JsonQuery(
expression: x.entity.EntityJson,
path: "$.Contact"),
ClientLocation = ContextExtensions.JsonQuery(
expression: x.entity.EntityJson,
path: "$.Location"),
SelectedVendor = x.vendor != null ? new MatchedVendor{
Id = x.vendor.Id,
Name = x.vendor.Name
}
: null,
MatchesCount = x.entity.Matches
.AsQueryable()
.Join(
inner: DbSet(),
outerKeySelector: match => match.Id,
innerKeySelector: matchResult => matchResult.MatchId,
resultSelector: (match, matchResult) => matchResult.MatchId)
.Distinct()
.Count(),
MatchedVendors = x.entity.Matches
.AsQueryable()
.Join(
inner: DbSet(),
outerKeySelector: match => match.Id,
innerKeySelector: matchResult => matchResult.MatchId,
resultSelector: (match, matchResult) => new {
matchResult = matchResult,
MatchAlgorithmTypeEnum = match.MatchAlgorithmTypeEnum
})
.Join(
inner: DbSetTableB(),
outerKeySelector: result => result.matchResult.MatchId,
innerKeySelector: vendor => vendor.Id,
resultSelector: (result, vendor) => new MatchedVendor{
Id = vendor.Id,
Confidence = result.matchResult.Confidence,
Name = vendor.Name,
AlgorithmTypeEnum = result.MatchTypeEnum
}
)
.ToList(),
Client = x.entity.JobMatch.Client.Name,
Job = x.entity.JobMatch.Name,
JobId = x.entity.JobMatchId,
DateAdded = x.entity.CreatedUTC
}
)
.Where(item => item.Status == Review)
.OrderBy(item => item.Name)
.Skip(__p_0)
.Take(__p_1)
.GroupBy(item => item.Name)
.OrderBy(group51285273 => group51285273.Key)
.Select(group51285273 => new AggregateFunctionsGroup{
Key = group51285273.Key,
ItemCount = group51285273
.AsQueryable()
.Count(),
HasSubgroups = False,
Member = "Name",
Items = group51285273
}
)' 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.

roji · 2024-09-20T16:17:40Z

@DushyantSinghChouhan can you please open a new issue with a minimal, runnable code sample for this?

ankitmatrix08 added the customer-reported label Feb 10, 2022

AndriySvyryd added the closed-duplicate label Feb 11, 2022

smitpatel marked this as a duplicate of #19929 Feb 11, 2022

AndriySvyryd closed this as completed Feb 12, 2022

AndriySvyryd reopened this Feb 14, 2022

AndriySvyryd added area-query ef6-parity labels Feb 14, 2022

smitpatel marked this as a duplicate of #26748 Feb 14, 2022

AndriySvyryd closed this as completed Feb 14, 2022

ajcvickers removed area-query ef6-parity labels Feb 14, 2022

ankitmatrix08 mentioned this issue Jun 23, 2022

Narrow the feature gap between EF Core and EF6 #24106

Open

33 tasks

ajcvickers reopened this Oct 16, 2022

ajcvickers closed this as not planned Won't fix, can't repro, duplicate, stale Oct 16, 2022

Provide feedback

Saved searches

Use saved searches to filter your results more quickly

Group By Failure - Translation of 'Select' which contains grouping parameter without composition is not supported. #27426

Group By Failure - Translation of 'Select' which contains grouping parameter without composition is not supported. #27426

ankitmatrix08 commented Feb 10, 2022

AndriySvyryd commented Feb 11, 2022

smitpatel commented Feb 11, 2022

AndriySvyryd commented Feb 12, 2022

ankitmatrix08 commented Feb 13, 2022

ankitmatrix08 commented Feb 14, 2022

smitpatel commented Feb 14, 2022

ankitmatrix08 commented Feb 14, 2022

AndriySvyryd commented Feb 14, 2022

ankitmatrix08 commented Feb 14, 2022

AndriySvyryd commented Feb 14, 2022

ajcvickers commented Feb 14, 2022

DushyantSinghChouhan commented Sep 20, 2024

roji commented Sep 20, 2024

Group By Failure - Translation of 'Select' which contains grouping parameter without composition is not supported. #27426

Group By Failure - Translation of 'Select' which contains grouping parameter without composition is not supported. #27426

Comments

ankitmatrix08 commented Feb 10, 2022

AndriySvyryd commented Feb 11, 2022

smitpatel commented Feb 11, 2022

AndriySvyryd commented Feb 12, 2022

ankitmatrix08 commented Feb 13, 2022

ankitmatrix08 commented Feb 14, 2022

smitpatel commented Feb 14, 2022

ankitmatrix08 commented Feb 14, 2022

AndriySvyryd commented Feb 14, 2022

ankitmatrix08 commented Feb 14, 2022

AndriySvyryd commented Feb 14, 2022

ajcvickers commented Feb 14, 2022

DushyantSinghChouhan commented Sep 20, 2024

roji commented Sep 20, 2024