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

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

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.

Liked this post? Empower your friends by sharing it!
Categories: Data platform

View Comments (6)

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

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

  • 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).

Related Post