Natural Sort in NHibernate

Question

I've got a hibernate query that returns a list of objects and I want to order by a title. This is a user maintained field and some of our customers like prefixing their titles with numbers, this isn't something I can control. The data is something like this:

 - 1 first thing
 - 2 second thing
 - 5 fifth thing
 - 10 tenth thing
 - 20 twentieth thing
 - A thing with no number

A traditional

.AddOrder(Order.Asc("Name"))

Is resulting in a textual sort:

 - 1 first thing
 - 10 tenth thing
 - 2 second thing
 - 20 twentieth thing
 - 5 fifth thing
 - A thing with no number

This is correct as this is a nvarchar field but is there any way I can get the numbers to sort as well?

There seem to be several workarounds involving prefixing all fields with leading 0's and such like but do any of these work through NHibernate?

This application runs interchangeably on Oracle and MsSQL.

Cheers,

Matt

Answer 1

Add additional field (e.g. named Name_Number ) to object defenition and query on server side.

For Oracle select in this field to_number(Name) as Name_Number .

For For MS SQL select in this field cast(Name as numeric) as Name_Number .

Then sort on client side as .AddOrder(Order.Asc("Name_Number"))

P.S. I am not sure, because don't have enough experience with hibernate/nhibernate and hate ORM at all :)

Answer 2

You might consider creating a custom sort order implementation that allows you to specify the collation on the column to achieve your desired results. Something along the lines of:

ORDER BY Name COLLATE Latin1_General_BIN ASC

But with an appropriate collation (I don't believe Latin1_General_BIN is what you need specifically)