Complex Ordering with NHibernate Criteria Queries

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 t开发者_StackOverflow社区he only option?

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".