Must be reducible node System.ArgumentException upgrading to EF Core 2.1.1 #12560

BlaiseD · 2018-07-05T15:06:56Z

The exception looks similar to #11933 but the query is different and the stack trace is not identical. In case the problem source is unrelated please find the description below.

The following works fine in 2.0.1:

        static void Main(string[] args)
        {
            IServiceProvider serviceProvider = new ServiceCollection().AddDbContext<SchoolContext>(options =>
                options.UseSqlServer("ConnectionString"), ServiceLifetime.Transient)
                .BuildServiceProvider();

            Func<IQueryable<Student>, object> aggregatesFunc2 = q => q.GroupBy(item => 1)
            .OrderBy(group32123533 => group32123533.Key)
            .Select(group32123533 => new
            {
                Key = group32123533.Key,
                ItemCount = group32123533.Count(),
                HasSubgroups = false,
                AggregateFunctionsProjection = new
                {
                    Count_lastName = q.Where(item => (1 == group32123533.Key)).Count(),
                    Min_enrollmentDate = q.Where(item => (1 == group32123533.Key)).Min(item => item.EnrollmentDate)
                },
                Member = ""
            }).First();

            using (SchoolContext context = serviceProvider.GetRequiredService<SchoolContext>())
            {
                object grp = aggregatesFunc2(context.Students);
            }
        }

After upgrading to Microsoft.EntityFrameworkCore.SqlServer version 2.1.1 from version 2.0.1, a System.ArgumentException gets thrown with the following message and stack trace.

Exception message:
Stack trace:
System.ArgumentException
  HResult=0x80070057
  Message=must be reducible node
  Source=System.Linq.Expressions
  StackTrace:
   at System.Linq.Expressions.Expression.ReduceAndCheck()
   at System.Linq.Expressions.Expression.ReduceExtensions()
   at System.Linq.Expressions.Compiler.StackSpiller.RewriteExtensionExpression(Expression expr, Stack stack)
   at System.Linq.Expressions.Compiler.StackSpiller.RewriteExpression(Expression node, Stack stack)
   at System.Linq.Expressions.Compiler.StackSpiller.RewriteMemberExpression(Expression expr, Stack stack)
   at System.Linq.Expressions.Compiler.StackSpiller.RewriteExpression(Expression node, Stack stack)
   at System.Linq.Expressions.Compiler.StackSpiller.ChildRewriter.Add(Expression expression)
   at System.Linq.Expressions.Compiler.StackSpiller.RewriteBinaryExpression(Expression expr, Stack stack)
   at System.Linq.Expressions.Compiler.StackSpiller.RewriteExpression(Expression node, Stack stack)
   at System.Linq.Expressions.Compiler.StackSpiller.RewriteExpressionFreeTemps(Expression expression, Stack stack)
   at System.Linq.Expressions.Compiler.StackSpiller.Rewrite[T](Expression`1 lambda)
   at System.Linq.Expressions.Expression`1.Accept(StackSpiller spiller)
   at System.Linq.Expressions.Compiler.StackSpiller.RewriteLambdaExpression(Expression expr)
   at System.Linq.Expressions.Compiler.StackSpiller.RewriteExpression(Expression node, Stack stack)
   at System.Linq.Expressions.Compiler.StackSpiller.ChildRewriter.Add(Expression expression)
   at System.Linq.Expressions.Compiler.StackSpiller.ChildRewriter.AddArguments(IArgumentProvider expressions)
   at System.Linq.Expressions.Compiler.StackSpiller.RewriteMethodCallExpression(Expression expr, Stack stack)
   at System.Linq.Expressions.Compiler.StackSpiller.RewriteExpression(Expression node, Stack stack)
   at System.Linq.Expressions.Compiler.StackSpiller.ChildRewriter.Add(Expression expression)
   at System.Linq.Expressions.Compiler.StackSpiller.ChildRewriter.AddArguments(IArgumentProvider expressions)
   at System.Linq.Expressions.Compiler.StackSpiller.RewriteMethodCallExpression(Expression expr, Stack stack)
   at System.Linq.Expressions.Compiler.StackSpiller.RewriteExpression(Expression node, Stack stack)
   at System.Linq.Expressions.Compiler.StackSpiller.ChildRewriter.Add(Expression expression)
   at System.Linq.Expressions.Compiler.StackSpiller.ChildRewriter.AddArguments(IArgumentProvider expressions)
   at System.Linq.Expressions.Compiler.StackSpiller.RewriteNewExpression(Expression expr, Stack stack)
   at System.Linq.Expressions.Compiler.StackSpiller.RewriteExpression(Expression node, Stack stack)
   at System.Linq.Expressions.Compiler.StackSpiller.ChildRewriter.Add(Expression expression)
   at System.Linq.Expressions.Compiler.StackSpiller.ChildRewriter.AddArguments(IArgumentProvider expressions)
   at System.Linq.Expressions.Compiler.StackSpiller.RewriteNewExpression(Expression expr, Stack stack)
   at System.Linq.Expressions.Compiler.StackSpiller.RewriteExpression(Expression node, Stack stack)
   at System.Linq.Expressions.Compiler.StackSpiller.RewriteExpressionFreeTemps(Expression expression, Stack stack)
   at System.Linq.Expressions.Compiler.StackSpiller.Rewrite[T](Expression`1 lambda)
   at System.Linq.Expressions.Expression`1.Accept(StackSpiller spiller)
   at System.Linq.Expressions.Compiler.StackSpiller.RewriteLambdaExpression(Expression expr)
   at System.Linq.Expressions.Compiler.StackSpiller.RewriteExpression(Expression node, Stack stack)
   at System.Linq.Expressions.Compiler.StackSpiller.ChildRewriter.Add(Expression expression)
   at System.Linq.Expressions.Compiler.StackSpiller.ChildRewriter.AddArguments(IArgumentProvider expressions)
   at System.Linq.Expressions.Compiler.StackSpiller.RewriteMethodCallExpression(Expression expr, Stack stack)
   at System.Linq.Expressions.Compiler.StackSpiller.RewriteExpression(Expression node, Stack stack)
   at System.Linq.Expressions.Compiler.StackSpiller.ChildRewriter.Add(Expression expression)
   at System.Linq.Expressions.Compiler.StackSpiller.ChildRewriter.AddArguments(IArgumentProvider expressions)
   at System.Linq.Expressions.Compiler.StackSpiller.RewriteMethodCallExpression(Expression expr, Stack stack)
   at System.Linq.Expressions.Compiler.StackSpiller.RewriteExpression(Expression node, Stack stack)
   at System.Linq.Expressions.Compiler.StackSpiller.RewriteExpressionFreeTemps(Expression expression, Stack stack)
   at System.Linq.Expressions.Compiler.StackSpiller.Rewrite[T](Expression`1 lambda)
   at System.Linq.Expressions.Expression`1.Accept(StackSpiller spiller)
   at System.Linq.Expressions.Compiler.StackSpiller.RewriteLambdaExpression(Expression expr)
   at System.Linq.Expressions.Compiler.StackSpiller.RewriteExpression(Expression node, Stack stack)
   at System.Linq.Expressions.Compiler.StackSpiller.ChildRewriter.Add(Expression expression)
   at System.Linq.Expressions.Compiler.StackSpiller.ChildRewriter.AddArguments(IArgumentProvider expressions)
   at System.Linq.Expressions.Compiler.StackSpiller.RewriteMethodCallExpression(Expression expr, Stack stack)
   at System.Linq.Expressions.Compiler.StackSpiller.RewriteExpression(Expression node, Stack stack)
   at System.Linq.Expressions.Compiler.StackSpiller.ChildRewriter.Add(Expression expression)
   at System.Linq.Expressions.Compiler.StackSpiller.ChildRewriter.AddArguments(IArgumentProvider expressions)
   at System.Linq.Expressions.Compiler.StackSpiller.RewriteMethodCallExpression(Expression expr, Stack stack)
   at System.Linq.Expressions.Compiler.StackSpiller.RewriteExpression(Expression node, Stack stack)
   at System.Linq.Expressions.Compiler.StackSpiller.RewriteExpressionFreeTemps(Expression expression, Stack stack)
   at System.Linq.Expressions.Compiler.StackSpiller.Rewrite[T](Expression`1 lambda)
   at System.Linq.Expressions.Expression`1.Accept(StackSpiller spiller)
   at System.Linq.Expressions.Compiler.LambdaCompiler.Compile(LambdaExpression lambda)
   at System.Linq.Expressions.Expression`1.Compile(Boolean preferInterpretation)
   at Microsoft.EntityFrameworkCore.Query.EntityQueryModelVisitor.CreateExecutorLambda[TResults]()
   at Microsoft.EntityFrameworkCore.Query.EntityQueryModelVisitor.CreateQueryExecutor[TResult](QueryModel queryModel)
   at Microsoft.EntityFrameworkCore.Query.Internal.QueryCompiler.CompileQueryCore[TResult](Expression query, IQueryModelGenerator queryModelGenerator, IDatabase database, IDiagnosticsLogger`1 logger, Type contextType)
   at Microsoft.EntityFrameworkCore.Query.Internal.QueryCompiler.<>c__DisplayClass13_0`1.<Execute>b__0()
   at Microsoft.EntityFrameworkCore.Query.Internal.CompiledQueryCache.GetOrAddQueryCore[TFunc](Object cacheKey, Func`1 compiler)
   at Microsoft.EntityFrameworkCore.Query.Internal.QueryCompiler.Execute[TResult](Expression query)
   at System.Linq.Queryable.First[TSource](IQueryable`1 source)
   at ConsoleAppEFCore21.Program.<>c.<Main>b__0_1(IQueryable`1 q) in C:\.Research\TestEFCore211\ConsoleAppEFCore21\Program.cs:line 21
   at ConsoleAppEFCore21.Program.Main(String[] args) in C:\.Research\TestEFCore211\ConsoleAppEFCore21\Program.cs:line 38

Commenting out both Count_lastName = q.Where(item => (1 == group32123533.Key)).Count() and
Min_enrollmentDate = q.Where(item => (1 == group32123533.Key)).Min(item => item.EnrollmentDate) gets rid of the exception.

Steps to reproduce

Create a console application with the following PropertyGroup and ItemGroup:

  <PropertyGroup>
    <OutputType>Exe</OutputType>
    <TargetFramework>netcoreapp2.1</TargetFramework>
  </PropertyGroup>

  <ItemGroup>
    <PackageReference Include="Microsoft.EntityFrameworkCore.SqlServer" Version="2.1.1" />
    <PackageReference Include="Microsoft.Extensions.Configuration" Version="2.1.1" />
    <PackageReference Include="Microsoft.Extensions.Configuration.Json" Version="2.1.1" />
  </ItemGroup>

Add the classes below:

    [Table("Student")]
    public class Student
    {
        [Key]
        public int ID { get; set; }
        public string LastName { get; set; }
        public string FirstName { get; set; }
        public DateTime EnrollmentDate { get; set; }
    }

    public class SchoolContext : DbContext
    {
        public SchoolContext(DbContextOptions<SchoolContext> options) : base(options)
        {
        }

        public DbSet<Student> Students { get; set; }
    }
}

Update the Main method using the code described at the top and run the code.

Further technical details

EF Core version: 2.1.1
Database Provider: SqlServer 2014
Operating system: Windows 10
IDE: Visual Studio 2017 15.7.4

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

sokhomhuy · 2018-10-26T09:28:48Z

I got this same issue how can i solve it after migrate from 2.0 to 2.1.1

BlaiseD · 2018-10-26T11:16:37Z

@sokhomhuy - I stayed with 2.0 - don't think there is a workaround in 2.1. The fix is scheduled for 3.0.0. Am I right @maumar ?

ajcvickers · 2018-10-26T15:56:14Z

@maumar @smitpatel Any workarounds here?

maumar · 2018-10-30T23:14:36Z

@sokhomhuy workaround is to specify ToList call after the groupby. This will basically revert to 2.0 behavior, where we were not trying to client evaluate the groupby.
@BlaiseD you are correct, the fix is scheduled for 3.0 as of now. However it's all subject to change so no guarantees until the fix is in.

BlaiseD · 2018-11-11T18:03:39Z

The workaround works @maumar . Thanks.

phil-debbs · 2019-01-31T14:59:40Z

Works fine @maumar . Thank you.

tmacharia · 2019-05-03T01:52:42Z

In my case, i had a select statement before the groupby function.

What worked for me was calling .ToList() after the select function and before the GroupBy() function.

vainolo · 2019-05-13T11:56:17Z

Similar problem happening to me on 2.2.4. This works:

var a = entity2.Where(e => e.Finish.Date >= currDate..AddYears(-1)).ToList();
var b = a.GroupBy(e => e.Parent)
                .Select(e => new { Id = e.Key, Quantity = e.Count(), Total = e.Sum(d => d.Value) });
            var c = b.ToList();

But I get ArgumentException: must be reducible node when done in only one statement

var a2 = entity2.Where(e => e.Finish.Date >= currDate.AddYears(-1))
                .GroupBy(e => e.Parent)
                .Select(e => new { Id = e.Key, Quantity = e.Count(), Total = e.Sum(g => g.Value) });
var c2 = a2.ToList();

What is causing the problem here? Shouldn't the expression compilation results be the same in both cases?

maumar · 2019-05-13T17:59:20Z

@vainolo there are number of issues with GroupBy translation and your example is hitting one of them. Adding ToList() at the end of query a causes every subsequent operation to be executed on the client - bypassing EFCore translation and avoiding all the associated bugs.

vainolo · 2019-05-13T18:18:47Z

Any ETA for a fix here?

maumar · 2019-05-13T18:43:36Z

@vainolo as of now the bug is scheduled for 3.0 release, so (if nothing changes) that should be sometime in September.

dharmaturtle · 2019-08-26T17:57:46Z

For Googler's, if you try to GroupBy something nested, e.g.

new EFExampleBugDb()
        .Comment
        .GroupBy(x => x.Post.BlogId)
        .ToList();

You'll get the following exception:

System.InvalidOperationException: EF.Property called with wrong property name.

At least I did on EFCore 3.0.0-preview8.

I'm relatively doubtful that this will be fixed by September, given its new status in the backlog.

I uploaded an example here.

maumar · 2019-08-26T20:30:28Z

@dharmaturtle this specific issue (navigation in GroupBy key) actually has been fixed and should be working when preview9 ships - cb043a2

dharmaturtle · 2019-08-26T23:02:07Z

Thanks for the news @maumar. I updated my example repo to the nightly build, and it is currently giving me the error System.InvalidOperationException: Client side GroupBy is not supported. for the following:

      new EFExampleBugDb()
        .Comment
        .GroupBy(x => x.PostId)
        .ToList();

Link.

I believe by having the .ToList() after the .GroupBy(), it evaluates in SQL, but it appears as though I'm mistaken. Is it because I'm using System.Linq? I see that error message in the commit, so it seems like I'm at least hitting it.

Here's the full stack trace:

System.InvalidOperationException: Client side GroupBy is not supported.
at Microsoft.EntityFrameworkCore.Query.RelationalShapedQueryCompilingExpressionVisitor.CustomShaperCompilingExpressionVisitor.VisitExtension(Expression extensionExpression)
at System.Linq.Expressions.Expression.Accept(ExpressionVisitor visitor)
at System.Linq.Expressions.ExpressionVisitor.Visit(Expression node)
at System.Dynamic.Utils.ExpressionVisitorUtils.VisitBlockExpressions(ExpressionVisitor visitor, BlockExpression block)
at System.Linq.Expressions.ExpressionVisitor.VisitBlock(BlockExpression node)
at System.Linq.Expressions.BlockExpression.Accept(ExpressionVisitor visitor)
at System.Linq.Expressions.ExpressionVisitor.Visit(Expression node)
at System.Linq.Expressions.ExpressionVisitor.VisitLambda[T](Expression1 node) at System.Linq.Expressions.Expression1.Accept(ExpressionVisitor visitor)
at System.Linq.Expressions.ExpressionVisitor.Visit(Expression node)
at Microsoft.EntityFrameworkCore.Query.RelationalShapedQueryCompilingExpressionVisitor.VisitShapedQueryExpression(ShapedQueryExpression shapedQueryExpression)
at Microsoft.EntityFrameworkCore.Query.ShapedQueryCompilingExpressionVisitor.VisitExtension(Expression extensionExpression)
at System.Linq.Expressions.Expression.Accept(ExpressionVisitor visitor)
at System.Linq.Expressions.ExpressionVisitor.Visit(Expression node)
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_01.<Execute>b__0() at Microsoft.EntityFrameworkCore.Query.Internal.CompiledQueryCache.GetOrAddQueryCore[TFunc](Object cacheKey, Func1 compiler)
at Microsoft.EntityFrameworkCore.Query.Internal.CompiledQueryCache.GetOrAddQuery[TResult](Object cacheKey, Func1 compiler) at Microsoft.EntityFrameworkCore.Query.Internal.QueryCompiler.Execute[TResult](Expression query) at Microsoft.EntityFrameworkCore.Query.Internal.EntityQueryProvider.Execute[TResult](Expression expression) at Microsoft.EntityFrameworkCore.Query.Internal.EntityQueryable1.GetEnumerator()
at System.Collections.Generic.List1..ctor(IEnumerable1 collection)
at System.Linq.Enumerable.ToList[TSource](IEnumerable`1 source)
at XUnitTestProject1.UnitTest1.Test1() in ...\EFCore3GroupByBug\UnitTest1.cs:line 9

maumar · 2019-08-26T23:32:25Z

@dharmaturtle currently EFCore only supports groupby translation that results in groupby on the server, so the query needs to have some sort of aggregate and/or project a grouping key. Client-side groupby is tracked here: #17068

Sorry about the confusion caused by the initial comment.

dharmaturtle · 2019-08-27T00:26:15Z

@maumar I believe my code does execute on the server. Here it is again for ease of reference:

new EFExampleBugDb().Comment.GroupBy(x => x.PostId).ToList();

This yields the Client side GroupBy is not supported. error even though the .ToList() occurs after the .GroupBy(), which I believe results in server side execution. I believe that the following results in client side exeuction:

new EFExampleBugDb().Comment.ToList().GroupBy(x => x.PostId).ToList();

This does not result in any errors, though likely the .GroupBy() isn't even executed because the database I'm querying is empty.

Furthermore, if I group by the object and not the Id, as below:

new EFExampleBugDb().Comment.GroupBy(x => x.Post).ToList();

I get the following error:

System.InvalidOperationException: The LINQ expression 'EntityShaperExpression:
EntityType: Post
ValueBufferExpression:
ProjectionBindingExpression: Inner
IsNullable: False
' could not be translated. Either rewrite the query in a form that can be translated, or switch to client evaluation explicitly by inserting a call to either AsEnumerable(), AsAsyncEnumerable(), ToList(), or ToListAsync(). See https://go.microsoft.com/fwlink/?linkid=2101038 for more information.
at Microsoft.EntityFrameworkCore.Query.RelationalQueryableMethodTranslatingExpressionVisitor.TranslateGroupingKey(Expression expression)
at Microsoft.EntityFrameworkCore.Query.RelationalQueryableMethodTranslatingExpressionVisitor.TranslateGroupBy(ShapedQueryExpression source, LambdaExpression keySelector, LambdaExpression elementSelector, LambdaExpression resultSelector)
at Microsoft.EntityFrameworkCore.Query.QueryableMethodTranslatingExpressionVisitor.VisitMethodCall(MethodCallExpression methodCallExpression)
at Microsoft.EntityFrameworkCore.Query.RelationalQueryableMethodTranslatingExpressionVisitor.VisitMethodCall(MethodCallExpression methodCallExpression)
at System.Linq.Expressions.MethodCallExpression.Accept(ExpressionVisitor visitor)
at System.Linq.Expressions.ExpressionVisitor.Visit(Expression node)
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_01.<Execute>b__0() at Microsoft.EntityFrameworkCore.Query.Internal.CompiledQueryCache.GetOrAddQueryCore[TFunc](Object cacheKey, Func1 compiler)
at Microsoft.EntityFrameworkCore.Query.Internal.CompiledQueryCache.GetOrAddQuery[TResult](Object cacheKey, Func1 compiler) at Microsoft.EntityFrameworkCore.Query.Internal.QueryCompiler.Execute[TResult](Expression query) at Microsoft.EntityFrameworkCore.Query.Internal.EntityQueryProvider.Execute[TResult](Expression expression) at Microsoft.EntityFrameworkCore.Query.Internal.EntityQueryable1.GetEnumerator()
at System.Collections.Generic.List1..ctor(IEnumerable1 collection)
at System.Linq.Enumerable.ToList[TSource](IEnumerable`1 source)
at XUnitTestProject1.UnitTest1.Test1() in ...\EFCore3GroupByBug\UnitTest1.cs:line 9

I believe that the "or switch to client evaluation" supports the idea that my query is running server side.

Could you please elaborate on what you mean by "the query needs to have some sort of aggregate and/or project a grouping key"? I believe the .GroupBy(x => x.PostId) means that the PostId is the grouping key, no?

Thanks for bearing with me on this.

maumar · 2019-08-27T01:47:06Z

The query itself is running on the server. What we mean by "client groupby" is groupby that doesn't directly get translated to the GROUP BY on the server.
Naively one could try to translate

new EFExampleBugDb().Comment.GroupBy(x => x.PostId)

into

SELECT * FROM Comments as x GROUP BY x.PostId

But this won't work - SQL Server throws the following error:

Column 'some_column_from_the_comment_table' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

In order to make the query translatable to group by you need something like this:

new EFExampleBugDb().Comment.GroupBy(x => x.PostId).Select(g => new { key = g.Key, aggregate = g.Count() }

In order to translate GroupBy query that doesn't have a Select, EF needs to fetch all the data it needs for the query and bucket results into groups on the client. That part is missing and is tracked by #17068. Basically we will do the equivalent of context.Comment.ToList().GroupBy(x => x.PostId).

So, looking at the first case:

new EFExampleBugDb().Comment.GroupBy(x => x.PostId).ToList();

EF query pipelines encounters GroupBy and determines that it can't fully translate it to server GROUP BY, and that it will need to perform bucketing of groups on the client (i.e. client group by), and for now it throws the error.

looking at this example:

new EFExampleBugDb().Comment.ToList().GroupBy(x => x.PostId).ToList();

EF doesn't see anything that happens after ToList(), so from our perspective the query is just:

new EFExampleBugDb().Comment.ToList();

that's why you are not seeing any errors here.

Wrt group by entity (the last example), its tracked by #15938

dharmaturtle · 2019-08-27T03:42:11Z

Ahhh, that makes many things clearer. Thanks for explaining so much @maumar !

breyed · 2019-11-03T13:02:59Z

What if you consider bucketing on the client side to be an inherent part of object-relational mapping rather than client side evaluation? Then group join would fall outside the no-implicit-client-side-evaluation safeguard. If there are a million comments, the developer who writes the query

new EFExampleBugDb().Comment.GroupBy(x => x.PostId)

expects to pull a million rows from the comment table. Building ILookup's hashtable is already considered to be part of the ORM overhead for the aggregate = g.Count() case. Translating the query into a left join and then putting the results into an ILookup hashtable is equally inexpensive per row. Contrast such a mapping with a case of filtering like

new EFExampleBugDb().Comment.Select(x => IsSpam(x))

where the developer is expecting 1000 results, but because IsSpam has to be evaluated on the client, EF Core would have to pull back all million of the comments to see which pass the predicate. This is the type of situation that causes mysterious performance problems for developers. But if you can perform an operation without fetching any more rows than the developer expects, even if the nomenclature of "group" in LINQ doesn't match "group" in SQL, go for it!

breyed · 2019-11-03T13:21:46Z

I thought of a downside to my previous post. Even though the grouping isn't expensive, the implicit switch to client-side evaluation can be confusing if the query contains subsequent operations. If the groupby is followed by a where, I can see it being difficult to generate a meaningful exception showing the user where the client-side evaluation occurred.

Maybe it's best to recommend that the developer explicitly call ToLookup to make the client-side transition clear. Whatever the recommended approach, it's important that it be clearly stated in the exception for the naive Comment.GroupBy(x => x.PostId) case.

robinwilson16 · 2019-11-03T14:23:19Z

What I was doing to get around this error was to wrap the server part in a ( ).ToListAsync() and then doing any further client side evaluation after this and then finally calling .ToList() at the end to be able to output to the page. Then if only a single result was expected I added .FirstOrDefault() onto the end.
The first ToList()/ToListAsync() causes EF Core to load the results on the server forcing any subsequent evaluation to happen on the client.

Certainly the errors were not very helpful in debugging it and I had to use SQL Profiler to see what EF Core was generating.
I was using this mainly for stored procedures when switching to .NET Core 3.0 to force client evaluation when it was trying to perform server evaluation and then running into issues generating invalid SQL statements, wrapping EXEC queries in a select and trying to run as a sub-select, an issue not present in earlier versions.
Not sure if this is helpful?

smitpatel · 2019-11-03T17:28:18Z

@breyed - See #17068

breyed · 2019-11-03T17:47:35Z

@smitpatel I'd seen #17068 and considered it in conjunction with this issue. Both seemed to stop at identifying that SQL GROUP is less versatile than LINQ GroupBy. Neither seem to address yet whether LINQ GroupBy should be implemented via a SQL JOIN with cheap ORM-side grouping.

smitpatel · 2019-11-21T00:01:53Z

I briefly went over all the queries posted in this issue.

With aggregates and not referencing we would translating it correctly.
Without aggregate or referencing anything from outer in aggregate operation, it would fail.

GroupBy queries with aggregates in which Where predicate or Distinct is applied on grouping before calling aggregate operator is being tracked in #17376 #18836

Anything client evaluating will not work as per #17068. If you are getting exception message and if it does not fit in any of the above bucket then please file a new issue.

Closing this issue as closed by design since query posted in first post is not something we can translate to server.

ajcvickers added customer-reported regression labels Jul 5, 2018

ajcvickers added this to the 2.2.0 milestone Jul 6, 2018

ajcvickers assigned maumar Jul 6, 2018

ajcvickers added the type-investigation label Jul 6, 2018

ajcvickers added propose-punt and removed propose-punt labels Aug 1, 2018

ajcvickers modified the milestones: 2.2.0, 3.0.0 Aug 3, 2018

ajcvickers added type-bug and removed type-investigation labels Aug 7, 2018

ajcvickers added the propose-punt label Jun 27, 2019

ajcvickers modified the milestones: 3.0.0, Backlog Jun 28, 2019

ajcvickers added punted-for-3.0 and removed propose-punt labels Jun 28, 2019

dharmaturtle mentioned this issue Aug 26, 2019

[Preview 8] EF Core - Operation is not valid due to the current state of the object #17428

Closed

roji mentioned this issue Aug 27, 2019

Query with GroupBy or GroupJoin throws exception #17068

Closed

smitpatel added the area-query label Nov 19, 2019

smitpatel closed this as completed Nov 21, 2019

smitpatel removed this from the Backlog milestone Nov 21, 2019

smitpatel unassigned maumar Nov 21, 2019

smitpatel added closed-by-design and removed regression labels Nov 21, 2019

dharmaturtle mentioned this issue Feb 12, 2020

Query with group by doesn't work after upgrading from ef core 2.2.6 to 3.1.1 #19894

Closed

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

Must be reducible node System.ArgumentException upgrading to EF Core 2.1.1 #12560

Must be reducible node System.ArgumentException upgrading to EF Core 2.1.1 #12560

BlaiseD commented Jul 5, 2018

sokhomhuy commented Oct 26, 2018

BlaiseD commented Oct 26, 2018

ajcvickers commented Oct 26, 2018

maumar commented Oct 30, 2018

BlaiseD commented Nov 11, 2018

phil-debbs commented Jan 31, 2019

tmacharia commented May 3, 2019

vainolo commented May 13, 2019

maumar commented May 13, 2019

vainolo commented May 13, 2019

maumar commented May 13, 2019

dharmaturtle commented Aug 26, 2019 •

edited

maumar commented Aug 26, 2019 •

edited

dharmaturtle commented Aug 26, 2019 •

edited

maumar commented Aug 26, 2019 •

edited

dharmaturtle commented Aug 27, 2019

maumar commented Aug 27, 2019

dharmaturtle commented Aug 27, 2019

breyed commented Nov 3, 2019

breyed commented Nov 3, 2019

robinwilson16 commented Nov 3, 2019

smitpatel commented Nov 3, 2019

breyed commented Nov 3, 2019

smitpatel commented Nov 21, 2019

Must be reducible node System.ArgumentException upgrading to EF Core 2.1.1 #12560

Must be reducible node System.ArgumentException upgrading to EF Core 2.1.1 #12560

Comments

BlaiseD commented Jul 5, 2018

Steps to reproduce

Further technical details

sokhomhuy commented Oct 26, 2018

BlaiseD commented Oct 26, 2018

ajcvickers commented Oct 26, 2018

maumar commented Oct 30, 2018

BlaiseD commented Nov 11, 2018

phil-debbs commented Jan 31, 2019

tmacharia commented May 3, 2019

vainolo commented May 13, 2019

maumar commented May 13, 2019

vainolo commented May 13, 2019

maumar commented May 13, 2019

dharmaturtle commented Aug 26, 2019 • edited

maumar commented Aug 26, 2019 • edited

dharmaturtle commented Aug 26, 2019 • edited

maumar commented Aug 26, 2019 • edited

dharmaturtle commented Aug 27, 2019

maumar commented Aug 27, 2019

dharmaturtle commented Aug 27, 2019

breyed commented Nov 3, 2019

breyed commented Nov 3, 2019

robinwilson16 commented Nov 3, 2019

smitpatel commented Nov 3, 2019

breyed commented Nov 3, 2019

smitpatel commented Nov 21, 2019

dharmaturtle commented Aug 26, 2019 •

edited

maumar commented Aug 26, 2019 •

edited

dharmaturtle commented Aug 26, 2019 •

edited

maumar commented Aug 26, 2019 •

edited