Sunday, 14 June 2009

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:

Id Name ParentPageId Position
1 Home null 0
2 Products 1 0
3 Guitars 2 0
4 Basses 2 1
5 Keyboards 2 3
6 Drums 2 4
7 Services 1 0
8 Guitar Tuition 7 0
9 Guitar Repairs 7 1

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:

Id Name ParentPageId Position
1 Home null 0
2 Products 1 0
3 Guitars 2 2
4 Basses 2 null
5 Keyboards 2 5
6 Drums 2 6
7 Services 1 7
8 Guitar Tuition 7 null
9 Guitar Repairs 7 4

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.

1 comment:

Kevin said...

Great example. I have been trying to come up with a solution for list of things that can be arbitrarily re-ordered by a user. The lists can contain hundreds of items.

Am I correct to assume that following this approach, when an item is inserted into the list, if cascade is set, then updates will be called for all of the children in the collection?

This is better than having NH issue deletes and inserts for all the items again just to re-order. Is this correct? Thanks!