c# - 将多个 ORDER BY 子句添加到 NHibernate 查询会引发异常

Question

也许我的 Linq 查询有问题，或者我遇到了 NHibernate 不支持的问题。无论哪种方式，这都是一个奇怪的问题。这是我的查询确实有效:

// Query for all ingredients, most used ingredients first
var ingredients = (from ing in session.Query<Ingredients>()
                   orderby ((from p in session.Query<RecipeIngredients>()
                            where p.Ingredient == ing
                            select p.RecipeIngredientId).Count()) descending
                   select new IngredientSource(ing.IngredientId, ing.DisplayName));

这会产生查询:

select
   ingredient0_.IngredientId as col_0_0_,
   ingredient0_.DisplayName as col_1_0_
from ingredients ingredient0_
order by (select cast(count(recipeingr1_.RecipeIngredientId) as int4) from recipeingredients recipeingr1_ where recipeingr1_.IngredientId=ingredient0_.IngredientId) desc

我真的不喜欢发生的奇怪的 cast() 事情，但我怀疑这会减慢速度。

不过，我还需要添加一个第二个订单。接下来需要按成分名称 对结果进行排序。所以我尝试了显而易见的方法:

var ingredients = (from ing in session.Query<Ingredients>()
                   orderby ((from p in session.Query<RecipeIngredients>()
                            where p.Ingredient == ing
                            select p.RecipeIngredientId).Count()) descending,
                            ing.DisplayName
                   select new IngredientSource(ing.IngredientId, ing.DisplayName));

这会抛出异常:

NHibernate.Hql.Ast.ANTLR.QuerySyntaxException was unhandled
  HResult=-2146232832
  Message=Exception of type 'Antlr.Runtime.MismatchedTreeNodeException' was thrown. [.Select[KitchenPC.DB.Models.Ingredients,KitchenPC.Context.IngredientSource](.ThenBy[KitchenPC.DB.Models.Ingredients,System.String](.OrderByDescending[KitchenPC.DB.Models.Ingredients,System.Int32](NHibernate.Linq.NhQueryable`1[KitchenPC.DB.Models.Ingredients], Quote((ing, ) => (.Count[System.Guid](.Select[KitchenPC.DB.Models.RecipeIngredients,System.Guid](.Where[KitchenPC.DB.Models.RecipeIngredients](NHibernate.Linq.NhQueryable`1[KitchenPC.DB.Models.RecipeIngredients], Quote((p, ) => (Equal(p.Ingredient, ing))), ), Quote((p, ) => (p.RecipeIngredientId)), ), ))), ), Quote((ing, ) => (ing.DisplayName)), ), Quote((ing, ) => (new IngredientSource(ing.IngredientId, ing.DisplayName, ))), )]
  Source=NHibernate
  QueryString=.Select[KitchenPC.DB.Models.Ingredients,KitchenPC.Context.IngredientSource](.ThenBy[KitchenPC.DB.Models.Ingredients,System.String](.OrderByDescending[KitchenPC.DB.Models.Ingredients,System.Int32](NHibernate.Linq.NhQueryable`1[KitchenPC.DB.Models.Ingredients], Quote((ing, ) => (.Count[System.Guid](.Select[KitchenPC.DB.Models.RecipeIngredients,System.Guid](.Where[KitchenPC.DB.Models.RecipeIngredients](NHibernate.Linq.NhQueryable`1[KitchenPC.DB.Models.RecipeIngredients], Quote((p, ) => (Equal(p.Ingredient, ing))), ), Quote((p, ) => (p.RecipeIngredientId)), ), ))), ), Quote((ing, ) => (ing.DisplayName)), ), Quote((ing, ) => (new IngredientSource(ing.IngredientId, ing.DisplayName, ))), )
  StackTrace:
       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.QueryTranslatorImpl.Compile(IDictionary`2 replacements, Boolean shallow)
       at NHibernate.Hql.Ast.ANTLR.ASTQueryTranslatorFactory.CreateQueryTranslators(IASTNode ast, String queryIdentifier, String collectionRole, Boolean shallow, IDictionary`2 filters, ISessionFactoryImplementor factory)
       at NHibernate.Hql.Ast.ANTLR.ASTQueryTranslatorFactory.CreateQueryTranslators(String queryIdentifier, IQueryExpression queryExpression, String collectionRole, Boolean shallow, IDictionary`2 filters, ISessionFactoryImplementor factory)
       at NHibernate.Engine.Query.HQLExpressionQueryPlan.CreateTranslators(String expressionStr, IQueryExpression queryExpression, String collectionRole, Boolean shallow, IDictionary`2 enabledFilters, ISessionFactoryImplementor factory)
       at NHibernate.Engine.Query.HQLExpressionQueryPlan..ctor(String expressionStr, IQueryExpression queryExpression, String collectionRole, Boolean shallow, IDictionary`2 enabledFilters, ISessionFactoryImplementor factory)
       at NHibernate.Engine.Query.HQLExpressionQueryPlan..ctor(String expressionStr, 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, NhLinqExpression& nhQuery)
       at NHibernate.Linq.DefaultQueryProvider.Execute(Expression expression)
       at NHibernate.Linq.DefaultQueryProvider.Execute[TResult](Expression expression)
       at Remotion.Linq.QueryableBase`1.GetEnumerator()
       at System.Collections.Generic.List`1..ctor(IEnumerable`1 collection)
       at System.Linq.Enumerable.ToList[TSource](IEnumerable`1 source)
       at KitchenPC.DB.DatabaseAdapter.LoadIngredientsForIndex() in c:\KitchenPC\DB\DatabaseAdapter.cs:line 172

我做错了什么？

Answer 1

我已将 Linq 查询转换为 ICriteria ( QueryOver<> ) 查询，这似乎有效。但是，代码相当困惑。或者，很棒，具体取决于您的兴趣。

Models.Ingredients ing = null;
int? count = null;
var popularity = QueryOver.Of<Models.RecipeIngredients>()
   .Where(p => p.Ingredient.IngredientId == ing.IngredientId)
   .ToRowCountQuery();

var ingredients = session.QueryOver<Models.Ingredients>(() => ing)
   .SelectList(list => list
      .Select(p => p.IngredientId)
      .Select(p => p.DisplayName)
      .SelectSubQuery(popularity).WithAlias(() => count)
   )
   .OrderByAlias(() => count).Desc()
   .ThenBy(p => p.DisplayName).Asc()
   .List<Object[]>()
   .Select(i => new IngredientSource((Guid)i[0], (String)i[1]));

这会产生与原始查询非常相似的查询:

SELECT
   this_.IngredientId as y0_,
   this_.DisplayName as y1_,
   (SELECT count(*) as y0_ FROM recipeingredients this_0_ WHERE this_0_.IngredientId = this_.IngredientId) as y2_
FROM ingredients this_
ORDER BY y2_ desc, this_.DisplayName asc