Using NHibernate order-by attribute to sort names history

I am writing application that besides other cool things lets users insert information about business contacts. My system is able to keep names history of contacts. In this posting I will show you how to use NHibernate mappings to keep names ordered logically.

Here is fragment of my class diagram. It is working draft, so don’t take it as an example of perfect solution. (To find out more about class structure represented here read my blog posting Modeling people and organizations: Class Party).

Company name history model

Here is what I want to achieve. I took this screenshot from my working draft.

Company name history table

The problem here is sort order of valid-to dates. If I just sort names by date to descending order then current name (we don’t know how long it is valid) is the last one. I needed order shown on the image above.

Here is how I achieved this order using NHibernate mapping file and MSSQL COALESCEfunction. The trick is simple – I used COALESCE to replace null with current date.

<?xml version="1.0" encoding="utf-8" ?>
hibernate-mapping xmlns="urn:nhibernate-mapping-2.2">
    name="OM.Core.Contacts.Company, OM.Core"
    extends="OM.Core.Contacts.Party, OM.Core"
    <key column="party_id" />
    <property name="RegistryCode" column ="registry_code"/>
    <!-- more properties here -->
<bag name="Names" table="company_names" cascade="all"
order-by="COALESCE(valid_to, GETDATE()) desc">
      <key column="company_id"/>
      <one-to-many class="OM.Core.Contacts.CompanyName, OM.Core"/>

I think it is good solution for sorting because sorting is done in database and we don’t have to write additional code to DAL to get collection sorted. Of course, it is not recommended to change (reassign, reorder) collections monitored by NHibernate. What I did here was simple – I just added order-by attribute to Names bag definition and let NHibernate do the rest.

Gunnar Peipman

Gunnar Peipman is ASP.NET, Azure and SharePoint fan, Estonian Microsoft user group leader, blogger, conference speaker, teacher, and tech maniac. Since 2008 he is Microsoft MVP specialized on ASP.NET.

    6 thoughts on “Using NHibernate order-by attribute to sort names history

    • December 27, 2009 at 5:21 pm

      Thanks for the tip. I have actually been in the same situation, ending up doing much more dirty approach.

    • December 27, 2009 at 7:21 pm

      Neat solution but I’m not crazy about it for two reasons: 1) It’s database specific and 2) Display order is the UI’s concern, not the model’s. Point 2 is the bigger worry because the sort is only in place when loading, it may be lost if the collection changes in memory.

    • December 28, 2009 at 1:40 am

      Thanks for feedback, Jamie! :)

      1) there are always database specific things while databases have differences
      2) nothing sais you cannot use different sort order or sorting in UI – it is up to you how you like to solve it.

      In my case one sort order is enough and as it is web application new data is requested after each modification. In my case it is not problem because names table is not very large (when compared to some other tables).

    • January 13, 2010 at 9:00 pm

      I cannot believe this is true!

    • February 1, 2010 at 11:04 pm

      I cannot believe this will work!

    Leave a Reply

    Your email address will not be published. Required fields are marked *