Posts Tagged ‘sorting’


This is my first post here, and I’d like to go right into medias res. The motivation is clear: I am a developer dealing with MS VS & nhibernate in order to map DB schemas. It is not a long time, since I am working on the topic, so I encountered a few troubles that can be shared, I guess. I carried the tests out using a sample schema on an Oracle 10g XE instance that I set up for this purpose.

Recently, I came across the problem that I needed to sort data upon retrieval. Nothing special there, one could say. But what I needed was sorting by:

a.) a custom field, provided by the user
b.) a custom order direction (asc/desc), also user provided

Unless I wanted to use the custom sort direction, HQL did the trick for me. I simply could create a query like:

Sample hql query using custom sort field

Just had to pass the customField parameter before calling the AllHouses query. However, when I try to pass also the sort direction, like in the following figure, any calls to the query fail.

Sample hql query using custom sort field and custom sort direction

At this point, any call to the query fails. The Oracle XE is obviously not able to assign the ASC/DESC sorting direction through a parameter. Hence, I came up w/ the following solution: Criteria queries! This way you can add custom order fields AND custom order directions.

In short words, it is simply done by using the AddOrder method of an ISession object. It takes as a parameter the field (custom provided by you), and has got two methods (Asc() and Desc()) for creating an Order object. The rest should be self-explanatory.

Here is how it works:

Criteria query using custom field and sort order

Simple, isn’t it? One more advantage is, that this way you are a bit more independent from DB-vendor-specific sorting possibilities. nhibernate will take care of creating the correct SQL syntax, no matter what the underlying DB is (as long as it’s supported, of course).

The nhibernate version I used for these tests is 1.2.

Stay tuned to the next time!


Read Full Post »