Nested group by results in "A recognition error occured"

[mloffer]

NHibernate 5.3.12
.NET Core 3.1.10

I am evaluating NHibernate as a replacement for EFCore in a reporting application. EFCore has several limitations we are running up against, among which is that nested group bys cannot translated to SQL, and instead must be performed in memory. I set up a test project to see if NHibernate could handle this particular task, but I'm getting the following exception:

Exception:
   at NHibernate.Hql.Ast.ANTLR.ErrorCounter.ThrowQueryException()
   at NHibernate.Hql.Ast.ANTLR.HqlSqlTranslator.Translate()
   at NHibernate.Hql.Ast.ANTLR.QueryTranslatorImpl.Analyze(String collectionRole)
   at NHibernate.Hql.Ast.ANTLR.QueryTranslatorImpl.DoCompile(IDictionary`2 replacements, Boolean shallow, String collectionRole)
   at NHibernate.Hql.Ast.ANTLR.ASTQueryTranslatorFactory.CreateQueryTranslators(IQueryExpression queryExpression, IASTNode ast, String queryIdentifier, String collectionRole, Boolean shallow, IDictionary`2 filters, ISessionFactoryImplementor factory)
   at NHibernate.Hql.Ast.ANTLR.ASTQueryTranslatorFactory.CreateQueryTranslators(IQueryExpression queryExpression, String collectionRole, Boolean shallow, IDictionary`2 filters, ISessionFactoryImplementor factory)
   at NHibernate.Engine.Query.QueryExpressionPlan.CreateTranslators(IQueryExpression queryExpression, String collectionRole, Boolean shallow, IDictionary`2 enabledFilters, ISessionFactoryImplementor factory)
   at NHibernate.Engine.Query.QueryExpressionPlan..ctor(IQueryExpression queryExpression, Boolean shallow, IDictionary`2 enabledFilters, ISessionFactoryImplementor factory)
   at NHibernate.Engine.Query.QueryPlanCache.GetHQLQueryPlan(IQueryExpression queryExpression, Boolean shallow, IDictionary`2 enabledFilters)
   at NHibernate.Impl.AbstractSessionImpl.GetHQLQueryPlan(IQueryExpression queryExpression, Boolean shallow)
   at NHibernate.Impl.AbstractSessionImpl.CreateQuery(IQueryExpression queryExpression)
   at NHibernate.Linq.DefaultQueryProvider.PrepareQuery(Expression expression, IQuery& query)
   at NHibernate.Linq.DefaultQueryProvider.ExecuteList[TResult](Expression expression)
   at NHibernate.Linq.NhQueryable`1.System.Collections.Generic.IEnumerable<T>.GetEnumerator()
   at System.Collections.Generic.List`1..ctor(IEnumerable`1 collection)
   at System.Linq.Enumerable.ToList[TSource](IEnumerable`1 source)
   at ODataService.Controllers.OrderController.Get() in C:\dev\XPO_how-to-implement-odata4-service-with-xpo-netcore\CS\ODataService\Controllers\OrderController.cs:line 25
   at Microsoft.Extensions.Internal.ObjectMethodExecutor.Execute(Object target, Object[] parameters)
   at Microsoft.AspNetCore.Mvc.Infrastructure.ActionMethodExecutor.SyncObjectResultExecutor.Execute(IActionResultTypeMapper mapper, ObjectMethodExecutor executor, Object controller, Object[] arguments)
   at Microsoft.AspNetCore.Mvc.Infrastructure.ControllerActionInvoker.InvokeActionMethodAsync()
   at Microsoft.AspNetCore.Mvc.Infrastructure.ControllerActionInvoker.Next(State& next, Scope& scope, Object& state, Boolean& isCompleted)
   at Microsoft.AspNetCore.Mvc.Infrastructure.ControllerActionInvoker.InvokeNextActionFilterAsync()

QueryString:
.GroupBy[System.Linq.IGrouping`2[[<>f__AnonymousType0`2[[System.Int32, System.Private.CoreLib, Version=4.0.0.0, Culture=neutral, PublicKeyToken=7cec85d7bea7798e],[System.Int32, System.Private.CoreLib, Version=4.0.0.0, Culture=neutral, PublicKeyToken=7cec85d7bea7798e]], ODataService, Version=1.0.0.0, Culture=neutral, PublicKeyToken=null],[ODataService.NHibernate.NOrderDetail, ODataService, Version=1.0.0.0, Culture=neutral, PublicKeyToken=null]], System.Linq, Version=4.2.2.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a,System.Int32, System.Private.CoreLib, Version=4.0.0.0, Culture=neutral, PublicKeyToken=7cec85d7bea7798e](.GroupBy[ODataService.NHibernate.NOrderDetail, ODataService, Version=1.0.0.0, Culture=neutral, PublicKeyToken=null,<>f__AnonymousType0`2[[System.Int32, System.Private.CoreLib, Version=4.0.0.0, Culture=neutral, PublicKeyToken=7cec85d7bea7798e],[System.Int32, System.Private.CoreLib, Version=4.0.0.0, Culture=neutral, PublicKeyToken=7cec85d7bea7798e]], ODataService, Version=1.0.0.0, Culture=neutral, PublicKeyToken=null](NHibernate.Linq.NhQueryable`1[ODataService.NHibernate.NOrderDetail], Quote((x, ) => (new <>f__AnonymousType0`2[[System.Int32, System.Private.CoreLib, Version=4.0.0.0, Culture=neutral, PublicKeyToken=7cec85d7bea7798e],[System.Int32, System.Private.CoreLib, Version=4.0.0.0, Culture=neutral, PublicKeyToken=7cec85d7bea7798e]], ODataService, Version=1.0.0.0, Culture=neutral, PublicKeyToken=null(x.Order.Id, x.Product.Id, ))), ), Quote((x, ) => (x.Key.ProductId)), )

I have a link to my sample project below, with the nested group by statement at OrderController:25. You can trigger this code by hitting

• https://localhost:5001/odata/initializedatabase (to populate database; only needed once)
• https://localhost:5001/odata/order

https://github.com/mloffer/XPO_how-to-implement-odata4-service-with-xpo-netcore

[hazzik]

A recognition error means that this is not supported.

[mloffer]

OK, thanks.

[hazzik]

Is it this query that does not work?

            Console.WriteLine("--- begin sql execution ---");
            var ntest = NSession.Query<NOrderDetail>()
                .GroupBy(x => new { OrderId = x.Order.Id, ProductId = x.Product.Id }) // this works fine
                .GroupBy(x => x.Key.ProductId) // exception: "A recognition error occurred"
                .ToList();
            Console.WriteLine("--- end sql execution ---");

[hazzik]

@mloffer, because you do not have any aggregates in your group-bys then you will be forced to select everything into memory.

[mloffer]

Thanks for the follow up, but we decided to stick with EF Core.

[hazzik]

@mloffer sure, it’s you choice.