Efcore: Must be reducible node System.ArgumentException upgrading to EF Core 2.1.1

[BlaiseD]

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

[sokhomhuy]

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

[BlaiseD]

@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]

@maumar @smitpatel Any workarounds here?

[maumar]

@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]

The workaround works @maumar . Thanks.

[phil-debbs]

Works fine @maumar . Thank you.

[tmacharia]

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]

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]

@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]

Any ETA for a fix here?

[maumar]

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

[dharmaturtle]

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]

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

[dharmaturtle]

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.VisitLambdaT
at Microsoft.EntityFrameworkCore.Query.Internal.CompiledQueryCache.GetOrAddQueryTResult
at System.Linq.Enumerable.ToListTSource
at XUnitTestProject1.UnitTest1.Test1() in ...\EFCore3GroupByBug\UnitTest1.cs:line 9

[maumar]

@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: https://github.com/aspnet/EntityFrameworkCore/issues/17068

Sorry about the confusion caused by the initial comment.

[dharmaturtle]

@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.CreateQueryExecutorTResult
at Microsoft.EntityFrameworkCore.Storage.Database.CompileQueryTResult
at Microsoft.EntityFrameworkCore.Query.Internal.QueryCompiler.CompileQueryCoreTResult
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.GetOrAddQueryTResult
at System.Linq.Enumerable.ToListTSource
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]

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 https://github.com/aspnet/EntityFrameworkCore/issues/15938

[dharmaturtle]

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

[breyed]

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

``` c#
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

``` c#
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]

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]

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]

@breyed - See #17068

[breyed]

@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]

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.