NHibernate OrderByDescending throwing exception `A recognition error occurred`

Question

When using:

.OrderByDescending(review => review.Country.Id == reviewCountryID)

the SQL query is generated correctly and no error is thrown.

When using:

.OrderByDescending(review =>
    review.User != null &&
    review.User.Country != null &&
    review.User.Country.Id == userCountryID
)

an exception is being thrown:

NHibernate.Hql.Ast.ANTLR.QuerySyntaxException
A recognition error occurred.

at NHibernate.Hql.Ast.ANTLR.ErrorCounter.ThrowQueryException()
at NHibernate.Hql.Ast.ANTLR.HqlSqlTranslator.Translate()
at NHibernate.Hql.Ast.ANTLR.QueryTranslatorImpl.DoCompile(IDictionary`2 replacements, Boolean shallow, String collectionRole)
at NHibernate.Hql.Ast.ANTLR.ASTQueryTranslatorFactory.CreateQueryTranslators(IASTNode ast, String queryIdentifier, String collectionRole, Boolean shallow, IDictionary`2 filters, 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, ref IQuery query, ref NhLinqExpression nhQuery)
at NHibernate.Linq.DefaultQueryProvider.Execute(Expression expression)
at NHibernate.Linq.DefaultQueryProvider.Execute(Expression expression)
at Remotion.Linq.QueryableBase`1.GetEnumerator()
at System.Collections.Generic.List`1..ctor(IEnumerable`1 collection)
at System.Linq.Enumerable.ToList(IEnumerable`1 source)

Any suggestions of how to fix this issue or an alternative?

Thank you!

Update

Updated the OrderBy to actually include the null checks.

Projecting in the Select() the expression review.User != null && review.User.Country != null && review.User.Country.Id == 144 works as intended, but the same conditions in the OrderBy throw the exception.

Update

Following fixes the issue for multiple joins but not when doing boolean conditions on them which is what the question asks for:

Do the OrderByDescending using the projected property and then project back only the entities queries on, for example:

.Select(review => new
{
    Review = review,
    ReviewUserCountryId = (review.User != null && review.User.Country != null) ? review.User.Country.Id : (int?)null
})
.OrderByDescending(review => ReviewUserCountryId)
.Select(reviewInfo => reviewInfo.Review) // for aliasing assuming that ReviewUserCountryId won't be needed later in the query
// rest of query

Hygrograph

what are you putting a boolean condition in orderBy, shouldnt boolean condition go in where clause and the orderBy should be just on a property you want to order?? Order by should be just .OrderByDescending(review => review.Country.Id) move other checks to where clause – Karlise

@entre: I do not need to filter by the condition, I just need to order by the condition being true descending. – Pulque

Answer 1

It seems that for multiple joins NHibernate refuses to order on booleans.

A solution is to sort on projection to integers in the OrderBy, for example:

.OrderByDescending(review => (review.User != null && review.User.Country != null && review.User.Country.Id == userCountryID) ? 42 : -42);

If there is a better solution please add it.

Hygrograph

Using 42 is always the preferred approach. – Industrialist