Efcore: This works with the InMemory Provider

[KlausEvenEnevoldsen]

We are working on a rather large system in ASP.NET Core with EF Core and Angular.

In my extensive unit tests I use the InMemory Provider and everything runs smoothly.

It is a relatively simple query which works fine with the InMemory Provider, but when I execute it with the SqlServer Provider it throws an exception.

public IQueryable<FinancialStatementLedgerAccountLine> LoadAllWithinDateInterval(DateTime fromDate, DateTime toDate)
        {
            var sumPostings = from la in Context.LedgerAccounts
                              from pt in la.PostingTransactions
                              where pt.PostingDate >= fromDate && pt.PostingDate <= toDate
                              group pt by pt.LedgerAccountId into postingSumGroup
                              select new { LedgerAccountId = postingSumGroup.Key, DebitAmount = postingSumGroup.Sum(p => p.DebitAmount), CreditAmount = postingSumGroup.Sum(p => p.CreditAmount) };

            var sumOpeningBalance = from la in Context.LedgerAccounts
                                    from pt in la.PostingTransactions
                                    where (pt.PostingDate < fromDate && la.AccountType == Shared.Enums.LedgerAccountTypes.Status) || (pt.PostingDate >= new DateTime(fromDate.Year, 1, 1) && pt.PostingDate < fromDate && la.AccountType == Shared.Enums.LedgerAccountTypes.Status)
                                    group pt by pt.LedgerAccountId into postingSumGroup
                                    select new { LedgerAccountId = postingSumGroup.Key, OpeningBalance = postingSumGroup.Sum(p => p.DebitAmount) - postingSumGroup.Sum(p => p.CreditAmount) };

            var countNumberOfPostings = from la in Context.LedgerAccounts
                                        from pt in la.PostingTransactions
                                        where pt.PostingDate >= fromDate && pt.PostingDate <= toDate
                                        group pt by pt.LedgerAccountId into countSum
                                        select new { Id = countSum.Key, NumberOfPostings = countSum.Count() };

            var q = from la in Context.LedgerAccounts

                    join sp in sumPostings on la.Id equals sp.LedgerAccountId into postings
                    from sp in postings.DefaultIfEmpty()

                    join sob in sumOpeningBalance on la.Id equals sob.LedgerAccountId into openingBalance
                    from sob in openingBalance.DefaultIfEmpty()

                    join nop in countNumberOfPostings on la.Id equals nop.Id into numberOfPostings
                    from nop in numberOfPostings.DefaultIfEmpty()

                    orderby la.AccountNumber
                    select new FinancialStatementLedgerAccountLine()
                    {
                        Id = la.Id,
                        AccountNumber = la.AccountNumber,
                        AccountName = la.AccountName,
                        AccountType = la.AccountType,
                        AddedUpFromAccountNumber = la.AddedUpFromThisLedgerAccount != null ? la.AddedUpFromThisLedgerAccount.AccountNumber : 0,
                        NumberOfPostingTransactions = nop != null ? nop.NumberOfPostings : 0,
                        AmountDebit = sp != null ? sp.DebitAmount : 0,
                        AmountCredit = sp != null ? sp.CreditAmount : 0,
                        OpeningBalance = sob != null ? sob.OpeningBalance : 0,
                    };

            return q.AsQueryable();
        }

Exception message:
ArgumentException: must be reducible node

Stack trace:

System.Linq.Expressions.Expression.ReduceAndCheck()
System.Linq.Expressions.Expression.ReduceExtensions()
System.Linq.Expressions.Compiler.StackSpiller.RewriteExtensionExpression(Expression expr, Stack stack)
System.Linq.Expressions.Compiler.StackSpiller.RewriteExpression(Expression node, Stack stack)
System.Linq.Expressions.Compiler.StackSpiller.RewriteMemberExpression(Expression expr, Stack stack)
System.Linq.Expressions.Compiler.StackSpiller.RewriteExpression(Expression node, Stack stack)
System.Linq.Expressions.Compiler.StackSpiller.RewriteExpressionFreeTemps(Expression expression, Stack stack)
System.Linq.Expressions.Compiler.StackSpiller.Rewrite<T>(Expression<T> lambda)
System.Linq.Expressions.Expression<TDelegate>.Accept(StackSpiller spiller)
System.Linq.Expressions.Compiler.StackSpiller.RewriteLambdaExpression(Expression expr)
System.Linq.Expressions.Compiler.StackSpiller.RewriteExpression(Expression node, Stack stack)
System.Linq.Expressions.Compiler.StackSpiller+ChildRewriter.Add(Expression expression)
System.Linq.Expressions.Compiler.StackSpiller+ChildRewriter.AddArguments(IArgumentProvider expressions)
System.Linq.Expressions.Compiler.StackSpiller.RewriteMethodCallExpression(Expression expr, Stack stack)
System.Linq.Expressions.Compiler.StackSpiller.RewriteExpression(Expression node, Stack stack)
System.Linq.Expressions.Compiler.StackSpiller+ChildRewriter.Add(Expression expression)
System.Linq.Expressions.Compiler.StackSpiller+ChildRewriter.AddArguments(IArgumentProvider expressions)
System.Linq.Expressions.Compiler.StackSpiller.RewriteMethodCallExpression(Expression expr, Stack stack)
System.Linq.Expressions.Compiler.StackSpiller.RewriteExpression(Expression node, Stack stack)
System.Linq.Expressions.Compiler.StackSpiller+ChildRewriter.Add(Expression expression)
System.Linq.Expressions.Compiler.StackSpiller.RewriteBinaryExpression(Expression expr, Stack stack)
System.Linq.Expressions.Compiler.StackSpiller.RewriteExpression(Expression node, Stack stack)
System.Linq.Expressions.Compiler.StackSpiller+ChildRewriter.Add(Expression expression)
System.Linq.Expressions.Compiler.StackSpiller+ChildRewriter.AddArguments(IArgumentProvider expressions)
System.Linq.Expressions.Compiler.StackSpiller.RewriteNewExpression(Expression expr, Stack stack)
System.Linq.Expressions.Compiler.StackSpiller.RewriteExpression(Expression node, Stack stack)
System.Linq.Expressions.Compiler.StackSpiller.RewriteExpressionFreeTemps(Expression expression, Stack stack)
System.Linq.Expressions.Compiler.StackSpiller.Rewrite<T>(Expression<T> lambda)
System.Linq.Expressions.Expression<TDelegate>.Accept(StackSpiller spiller)
System.Linq.Expressions.Compiler.StackSpiller.RewriteLambdaExpression(Expression expr)
System.Linq.Expressions.Compiler.StackSpiller.RewriteExpression(Expression node, Stack stack)
System.Linq.Expressions.Compiler.StackSpiller+ChildRewriter.Add(Expression expression)
System.Linq.Expressions.Compiler.StackSpiller+ChildRewriter.AddArguments(IArgumentProvider expressions)
System.Linq.Expressions.Compiler.StackSpiller.RewriteMethodCallExpression(Expression expr, Stack stack)
System.Linq.Expressions.Compiler.StackSpiller.RewriteExpression(Expression node, Stack stack)
System.Linq.Expressions.Compiler.StackSpiller+ChildRewriter.Add(Expression expression)
System.Linq.Expressions.Compiler.StackSpiller+ChildRewriter.AddArguments(IArgumentProvider expressions)
System.Linq.Expressions.Compiler.StackSpiller.RewriteMethodCallExpression(Expression expr, Stack stack)
System.Linq.Expressions.Compiler.StackSpiller.RewriteExpression(Expression node, Stack stack)
System.Linq.Expressions.Compiler.StackSpiller+ChildRewriter.Add(Expression expression)
System.Linq.Expressions.Compiler.StackSpiller+ChildRewriter.AddArguments(IArgumentProvider expressions)
System.Linq.Expressions.Compiler.StackSpiller.RewriteMethodCallExpression(Expression expr, Stack stack)
System.Linq.Expressions.Compiler.StackSpiller.RewriteExpression(Expression node, Stack stack)
System.Linq.Expressions.Compiler.StackSpiller+ChildRewriter.Add(Expression expression)
System.Linq.Expressions.Compiler.StackSpiller+ChildRewriter.AddArguments(IArgumentProvider expressions)
System.Linq.Expressions.Compiler.StackSpiller.RewriteMethodCallExpression(Expression expr, Stack stack)
System.Linq.Expressions.Compiler.StackSpiller.RewriteExpression(Expression node, Stack stack)
System.Linq.Expressions.Compiler.StackSpiller+ChildRewriter.Add(Expression expression)
System.Linq.Expressions.Compiler.StackSpiller+ChildRewriter.AddArguments(IArgumentProvider expressions)
System.Linq.Expressions.Compiler.StackSpiller.RewriteMethodCallExpression(Expression expr, Stack stack)
System.Linq.Expressions.Compiler.StackSpiller.RewriteExpression(Expression node, Stack stack)
System.Linq.Expressions.Compiler.StackSpiller+ChildRewriter.Add(Expression expression)
System.Linq.Expressions.Compiler.StackSpiller+ChildRewriter.AddArguments(IArgumentProvider expressions)
System.Linq.Expressions.Compiler.StackSpiller.RewriteMethodCallExpression(Expression expr, Stack stack)
System.Linq.Expressions.Compiler.StackSpiller.RewriteExpression(Expression node, Stack stack)
System.Linq.Expressions.Compiler.StackSpiller+ChildRewriter.Add(Expression expression)
System.Linq.Expressions.Compiler.StackSpiller+ChildRewriter.AddArguments(IArgumentProvider expressions)
System.Linq.Expressions.Compiler.StackSpiller.RewriteMethodCallExpression(Expression expr, Stack stack)
System.Linq.Expressions.Compiler.StackSpiller.RewriteExpression(Expression node, Stack stack)
System.Linq.Expressions.Compiler.StackSpiller.RewriteExpressionFreeTemps(Expression expression, Stack stack)
System.Linq.Expressions.Compiler.StackSpiller.Rewrite<T>(Expression<T> lambda)
System.Linq.Expressions.Expression<TDelegate>.Accept(StackSpiller spiller)
System.Linq.Expressions.Compiler.LambdaCompiler.Compile(LambdaExpression lambda)
System.Linq.Expressions.Expression<TDelegate>.Compile(bool preferInterpretation)
System.Linq.Expressions.Expression<TDelegate>.Compile()
Microsoft.EntityFrameworkCore.Query.EntityQueryModelVisitor.CreateExecutorLambda<TResults>()
Microsoft.EntityFrameworkCore.Query.RelationalQueryModelVisitor.CreateExecutorLambda<TResults>()
Microsoft.EntityFrameworkCore.Query.EntityQueryModelVisitor.CreateQueryExecutor<TResult>(QueryModel queryModel)
Microsoft.EntityFrameworkCore.Storage.Database.CompileQuery<TResult>(QueryModel queryModel)
Microsoft.EntityFrameworkCore.Query.Internal.QueryCompiler.CompileQueryCore<TResult>(Expression query, IQueryModelGenerator queryModelGenerator, IDatabase database, IDiagnosticsLogger<Query> logger, Type contextType)
Microsoft.EntityFrameworkCore.Query.Internal.QueryCompiler+<>c__DisplayClass13_0<TResult>.<Execute>b__0()
Microsoft.EntityFrameworkCore.Query.Internal.CompiledQueryCache.GetOrAddQueryCore<TFunc>(object cacheKey, Func<Func<QueryContext, TFunc>> compiler)
Microsoft.EntityFrameworkCore.Query.Internal.CompiledQueryCache.GetOrAddQuery<TResult>(object cacheKey, Func<Func<QueryContext, TResult>> compiler)
Microsoft.EntityFrameworkCore.Query.Internal.QueryCompiler.Execute<TResult>(Expression query)
Microsoft.EntityFrameworkCore.Query.Internal.EntityQueryProvider.Execute<TResult>(Expression expression)
Remotion.Linq.QueryableBase<T>.GetEnumerator()
System.Collections.Generic.List<T>.AddEnumerable(IEnumerable<T> enumerable)
System.Linq.Enumerable.ToList<TSource>(IEnumerable<TSource> source)
Komit.Services.Accounting.GeneralLedger.FinancialStatement.FinancialStatementService.Calculate(DateTime fromDate, DateTime toDate, int accountNumberRevenueInOperations, int accountNumberRevenueInStatus) in FinancialStatementService.cs
+
            var lines = q.ToList();
Komit.Web.Areas.Accounting.Controllers.FinancialStatementController.SimpleReport(FinancialStatementService service) in FinancialStatementController.cs
+
            var x = new FinancialStatementSimpleReportDto("Saldobalance", service.Calculate(new System.DateTime(2018, 01, 01), new System.DateTime(2018, 12, 31), 9995, 20900), service.Filter);
Microsoft.AspNetCore.Mvc.Internal.ActionMethodExecutor+TaskOfIActionResultExecutor.Execute(IActionResultTypeMapper mapper, ObjectMethodExecutor executor, object controller, object[] arguments)
System.Threading.Tasks.ValueTask<TResult>.get_Result()
Microsoft.AspNetCore.Mvc.Internal.ControllerActionInvoker.InvokeActionMethodAsync()
Microsoft.AspNetCore.Mvc.Internal.ControllerActionInvoker.InvokeNextActionFilterAsync()
Microsoft.AspNetCore.Mvc.Internal.ControllerActionInvoker.Rethrow(ActionExecutedContext context)
Microsoft.AspNetCore.Mvc.Internal.ControllerActionInvoker.Next(ref State next, ref Scope scope, ref object state, ref bool isCompleted)
Microsoft.AspNetCore.Mvc.Internal.ControllerActionInvoker.InvokeInnerFilterAsync()
Microsoft.AspNetCore.Mvc.Internal.ResourceInvoker.InvokeNextResourceFilter()
Microsoft.AspNetCore.Mvc.Internal.ResourceInvoker.Rethrow(ResourceExecutedContext context)
Microsoft.AspNetCore.Mvc.Internal.ResourceInvoker.Next(ref State next, ref Scope scope, ref object state, ref bool isCompleted)
Microsoft.AspNetCore.Mvc.Internal.ResourceInvoker.InvokeFilterPipelineAsync()
Microsoft.AspNetCore.Mvc.Internal.ResourceInvoker.InvokeAsync()
Microsoft.AspNetCore.Builder.RouterMiddleware.Invoke(HttpContext httpContext)
Komit.Web.Extensions.Middleware.AuthorizationMiddleware.Invoke(HttpContext context) in AuthorizationMiddleware.cs
+
            await Next.Invoke(context);
Microsoft.AspNetCore.Authentication.AuthenticationMiddleware.Invoke(HttpContext context)
Microsoft.AspNetCore.StaticFiles.StaticFileMiddleware.Invoke(HttpContext context)
Microsoft.AspNetCore.Diagnostics.EntityFrameworkCore.MigrationsEndPointMiddleware.Invoke(HttpContext context)
Microsoft.AspNetCore.Diagnostics.EntityFrameworkCore.DatabaseErrorPageMiddleware.Invoke(HttpContext httpContext)
Microsoft.AspNetCore.Diagnostics.EntityFrameworkCore.DatabaseErrorPageMiddleware.Invoke(HttpContext httpContext)
Microsoft.AspNetCore.Diagnostics.DeveloperExceptionPageMiddleware.Invoke(HttpContext context)

Steps to reproduce

Further technical details

EF Core version: 2.1.2
Database Provider: Microsoft.EntityFrameworkCore.SqlServer

[ajcvickers]

@KlausEvenEnevoldsen This looks like a bug, but we have not yet reproduced the issue. Please post a runnable project/solution or complete code listing that demonstrates the behavior you are seeing. If that seems infeasible, then please at least post the code for the entity types involved.

[KlausEvenEnevoldsen]

@ajcvickers Here you go...

Attached is a solution which isolates the problem, complete with a few unit tests which works with the InMemory Provider.

There is a small .bacpac-file containing the database and some demo-data (DemoDb.bacpac).

Let me know if you need anything else from me...

ConsoleApp1.zip

[KlausEvenEnevoldsen]

Do you have any news to share on this Issue? :-)

[ajcvickers]

@KlausEvenEnevoldsen This issue has been triaged as a bug into the 3.0 milestone. Thanks for providing the additional info.

[divega]

We revisited this issue in triage last week: The repro (left outer) joins a table with a few group by queries with aggregates. SQL translation for this scenario wasn't supported in 2.x and only worked with in-memory database because it was completely evaluated in memory.

Unfortunately, at this stage we don't think what we will be able to build SQL translation support for this scenario in 3.0. In fact, we expect this will start failing in the new in-memory database implementation because of how in 3.0 we restrict in-memory processing.

In the meantime, we should recommend for complex queries like this to split into several separate queries that are then merged together explicitly in memory using LINQ to objects, or to handcraft the corresponding SQL and execute it using FromSql functionality.

[KlausEvenEnevoldsen]

We changed to code some time ago, it runs as a database view now. 😊

We are in the process of testing out .net core 3.0 preview 6. I guess time will tell if the InMemoryProvider will cause new errors in our Unit Tests.

[smitpatel]

I downloaded the repro project attached in zip file. It fails as reported above. Once I upgrade all the packages for 3.1 release, everything passes. Closing this issue as fixed.
The generated SQL for above in 3.1 is as follows

exec sp_executesql N'SELECT [l].[Id], [l].[AccountNumber], [l].[AccountName], [l].[AccountType], [l3].[Id], [l3].[AccountName], [l3].[AccountNumber], [l3].[AccountType], [l3].[AddedUpFromThisLedgerAccountId], [l3].[Dimension1RequiredWhenPosting], [l3].[Dimension2RequiredWhenPosting], [l3].[Dimension3RequiredWhenPosting], [t1].[LedgerAccountId], [t1].[c], [t].[LedgerAccountId], [t].[c], [t].[c0], [t0].[LedgerAccountId], [t0].[c]
FROM [Accounting].[LedgerAccount] AS [l]
LEFT JOIN (
    SELECT [p].[LedgerAccountId], SUM([p].[DebitAmount]) AS [c], SUM([p].[CreditAmount]) AS [c0]
    FROM [Accounting].[LedgerAccount] AS [l0]
    INNER JOIN [Accounting].[PostingTransaction] AS [p] ON [l0].[Id] = [p].[LedgerAccountId]
    WHERE ([p].[PostingDate] >= @__fromDate_0) AND ([p].[PostingDate] <= @__toDate_1)
    GROUP BY [p].[LedgerAccountId]
) AS [t] ON [l].[Id] = [t].[LedgerAccountId]
LEFT JOIN (
    SELECT [p0].[LedgerAccountId], SUM([p0].[DebitAmount]) - SUM([p0].[CreditAmount]) AS [c]
    FROM [Accounting].[LedgerAccount] AS [l1]
    INNER JOIN [Accounting].[PostingTransaction] AS [p0] ON [l1].[Id] = [p0].[LedgerAccountId]
    WHERE (([p0].[PostingDate] < @__fromDate_0) AND ([l1].[AccountType] = 2)) OR ((([p0].[PostingDate] >= @__p_2) AND ([p0].[PostingDate] < @__fromDate_0)) AND ([l1].[AccountType] = 2))
    GROUP BY [p0].[LedgerAccountId]
) AS [t0] ON [l].[Id] = [t0].[LedgerAccountId]
LEFT JOIN (
    SELECT [p1].[LedgerAccountId], COUNT(*) AS [c]
    FROM [Accounting].[LedgerAccount] AS [l2]
    INNER JOIN [Accounting].[PostingTransaction] AS [p1] ON [l2].[Id] = [p1].[LedgerAccountId]
    WHERE ([p1].[PostingDate] >= @__fromDate_0) AND ([p1].[PostingDate] <= @__toDate_1)
    GROUP BY [p1].[LedgerAccountId]
) AS [t1] ON [l].[Id] = [t1].[LedgerAccountId]
LEFT JOIN [Accounting].[LedgerAccount] AS [l3] ON [l].[AddedUpFromThisLedgerAccountId] = [l3].[Id]
ORDER BY [l].[AccountNumber]',N'@__fromDate_0 datetime2(7),@__toDate_1 datetime2(7),@__p_2 datetime2(7)',@__fromDate_0='2018-01-01 00:00:00',@__toDate_1='2018-12-31 00:00:00',@__p_2='2018-01-01 00:00:00'