Complex Ordering with NHibernate Criteria Queries

Question

Came across something like this today, and was wondering if there was an equivalent way to solve it in criteria queries.

Basically, there is a table, and this table has two nullable string fields (foo and bar). We wish to sort by the aggregate of these.

string concatenation (order by foo + bar desc) returns null if one of the values is null, and order by foo, bar doesn't take into account nulls as we wish.

a solution in sql could look like :

SELECT foo, bar, (ISNULL(foo,'') + ISNULL(bar,'')) as f
FROM foobar
ORDER BY f DESC

I'm not sure of the details within nhibernate's tokenizer, but if you were to write it as

SELECT foo, bar
FROM foobar
ORDER BY (ISNULL(foo,'') + ISNULL(bar,'')) DESC

using the same query method it chokes because it parses both foo and bar as individual sort expressions.

So...how would you write that in a criteria query? Is there such a method, or would a new ResultTransformer or after-the-fact sorting be the only option?

Answer 1

You should create query-only properties (access="noop", see http://ayende.com/Blog/archive/2009/06/10/nhibernate-ndash-query-only-properties.aspx) with the corresponding formula.

For example,

<property name="SortableFoo" formula="ISNULL(foo,'')"/>

And you can now AddOrder with that "Property".