CODEWITHSUNDEEP
c#nhibernatelinq-to-nhibernate
Răzvan Flavius Panda
Răzvan Flavius Panda

Reputation: 22106

NHibernate OrderByDescending throwing exception `A recognition error occurred`

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

Upvotes: 5

Views: 9698

Answers (1)

Răzvan Flavius Panda
Răzvan Flavius Panda

Reputation: 22106

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.

Upvotes: 11

Related Questions