Case-insensitive sort ordering in NHibernate

Question

Consider the following criteria query:

var x = SomeCriteria.AddOrder(new Order("Name", true)).List();

This will order the result set by the Name property, but case sensitive:

"A1"
"B1"
"a2"

Any ideas how to add the order case insensitive so result "a2" will end up before "B1"?

Answer 1

You should be able to accomplish this by ordering on a projection that normalizes the case for you. For example, Oracle has a "lower" function that will lower case string data types like varchar2 and nvarchar2; so I will use this sql function to form a projection that will order appropriately.

var projection = Projections.SqlFunction("lower", 
                                         NHibernateUtil.String, 
                                         Projections.Property("Name"));

var x = SomeCriteria.AddOrder(Orders.Asc(projection)).List()

If you're using SQL Server, I'd recommend using the "upper" function instead of "lower" for efficiency. Microsoft has optimized its native code for performing uppercase comparisons, where the rest of the world seems to have optimized on lowercase.

Answer 2

Hibernate (Java) has an "ignoreCase()" method on the "Order" class, but it looks like NHibernate does not have this method on its "Order."

This is how I was thinking you could do it:

var x = SomeCriteria.AddOrder(new Order("Name", true).IgnoreCase()).List();

But unfortunately, there is no IgnoreCase().

As a workaround, you could use an HQL or SQL query - either of those should allow you to order case-insensitive.