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

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

About this issue

Original URL
State: closed
Created 6 years ago
Reactions: 2
Comments: 24 (9 by maintainers)

Most upvoted comments

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

maumar on Oct 30, 2018

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

maumar on Aug 27, 2019

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

maumar on May 13, 2019

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

maumar on Aug 26, 2019

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

maumar on Aug 26, 2019

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

maumar on May 13, 2019