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 COALESCE function. 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">
<joined-subclass
name="OM.Core.Contacts.Company, OM.Core"
extends="OM.Core.Contacts.Party, OM.Core"
table="company"
>
<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"/>
</bag>
</joined-subclass>
</hibernate-mapping>
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.