Managing Loosely-Ordered Collections : Lopsided Fun with NHibernate

NHibernate’s One-To-Many mapping allows you to map database one-to-many relationships into various kinds of .NET collections, but I recently hit a bit of a snag regarding the ordering of the collections. For example, consider a simple content management system for a music store’s website. This table:

represents a simple page-tree structure that looks like this:

Home
    +  Products
        + Guitars
        + Basses
        + Keyboards
        + Drums
    + Services
        + Guitar Tuition
        + Guitar Repairs

The key here is that the order of the elements is controlled by the Position column, so I can change the order of any page’s child pages. NHibernate can cope with this just fine by using an indexed collection – but this only works as long as the Position column is always populated with unique, sequential, non-null values. Null Position values cause it to blow up. It’ll also leave gaps in the list if there’s gaps in your sequence – five records with Position values 1,2,4,5,8 will be mapped into a nine-element list with NULL entries at indices 0,3,6,7, which turns your foreach() loops into little baby minefields and requires liberal use of guard clauses.

That’s not quite what I’m after here. All I want to do is preserve the order of elements if an order has been defined. There’s other apps talking to this database that aren’t using NH, which respect ordering by Position when retrieving records but don’t necessarily use sequential zero-based indices when saving changes. The actual data I’m dealing with is often going to look more like this:

All I’m after is that when I retrieve elements, they are ordered by the Position column (using whatever ORDER BY semantics are in use on the database server), and that if I move things around in the list and then save it, the order of my list is preserved when saving. I don’t care about null values – if there’s no explicit positions defined, just stick whatever you’ve got in a list in any old order and give it back to me. Likewise duplicate values are OK, and if there’s missing values, don’t give me NULLs, just skip to the next element.

One possible solution I’ve come up with looks like this. First, define a Position property on the entity. The getter returns the item’s current index in the parent’s children collection; and the setter is private and does nothing (but NHibernate won’t let you leave it out)

public virtual int Position {
    // Getter returns the current index of this element in its parent's children collection.
    get {
        if (this.Parent == null) return (0);
        if (this.Parent.Children == null) return (0);
        return (this.Parent.Children.IndexOf(this));
    }
    // Setter does nothing - order is determined by the "order-by" attribute in NHibernate mappings,
    // but NH requires that the setter exists.
    private set { }
}

Then there’s the actual mapping. We want to map this Position property to a DB column, so when doing insert/update operations, the value is persisted to the Position column, and we want to add an order-by attribute to the NHibernate mapping so that when we retrieve the collection, it comes back in the right order. If you’re using the lovely functional goodness that is Fluent NHibernate, the mapping looks like this:

public class CmsPageMap : ClassMap<CmsPage> {
    public CmsPageMap() {
        Map(page => page.Position);
        HasMany<CmsPage>(p => p.Children)
            .KeyColumnNames.Add("ParentPageId")
            .WithForeignKeyConstraintName("Page_Parent_Children")
            .Inverse()
            .Cascade.AllDeleteOrphan()
            // Using SetAttribute() here because Fluent NHibernate doesn't support order-by yet.
            .SetAttribute("order-by", "Position");

and if you’re mapping it using XML files, you’ll need something like this:

<property name="Position" type="Int32">
  <column name="Position" />
</property>
<many-to-one name="Parent" column="ParentPageId" />
<bag name="Children" inverse="true" cascade="all-delete-orphan" order-by="Position">
  <key foreign-key="Page_Parent_Children" column="ParentPageId" />
  <one-to-many class="NinjaCms.Shared.Model.CmsPage, NinjaCms.Shared" />
</bag>

The asymmetry of having a property that’s an order-by on the way out and a column mapping on the way back is slightly weird, but it works.