vrijdag 15 mei 2009

Forcing NHibernate to cascade deletes before updates

At work, I've ran into a particular problem; let me describe the situation at hand.

Suppose I have the following DB schema:

In the OrderLines table, there exists a UNIQUE CONSTRAINT on ( OrderId, SequenceNumber)

Next to this DB schema, I have an Order and an OrderLine class.
The Order class has a collection of OrderLines:

public class Order
public int Id
private set;

public ISet OrderLines = new HashedSet();

public void AddOrderLine( OrderLine ol )
ol.Order = this;
OrderLines.Add (ol);

I've them mapped using NHibernate so that I can save them in the above DB schema.
In the mapping, I've specified that the OrderLines collection should be cascaded when the Order is saved:

<set name="OrderLines" cascade="all-delete-orphan">
<key column="OrderId" />
<one-to-many class="OrderLine" />

Now, all goes well until you remove an OrderLine, add a new OrderLine with the same sequencenumber as the one that you've just removed.

Instead of first removing the OrderLine that you want to remove, and inserting the new OrderLine afterwards, NHibernate will perform these actions just the other way around:

It will first try to insert the new OrderLine, and then it will remove the existing Orderline.
Now, since we have a unique constraint in the DB, this will fail offcourse.

I've found a way to work around this problem. Although I find it not optimal (rather an ugly hack), it kinda works, so I'll stick with it for now.
This is how I've done it:

  • Instead of specifying 'all-delete-orphan' as the cascade option for the collection, I've modified it to 'delete-orphan' instead.
    This means that deletes will be cascaded, but inserts & updates are not

  • All DB access goes through 'repositories' in my application. This means I have an OrderRepository which has a Save method.
    I have modified my Save method so it looks like this:

    public void Save( Order o )
    // With.Transaction only starts a transaction when the given session
    // is not in a transaction yet.
    // session is an ISession and is a member variable of my Repository.
    With.Transaction (session, new delegate()
    session.SaveOrUpdate (o);


    foreach( OrderLine ol in o.OrderLines )
    session.SaveOrUpdate (ol);
    What I do here, is just calling SaveOrUpdate on the given Order. Because of the cascading setting, only the OrderLines that are to be deleted, will be cascaded.
    Afterwards, I call the Flush method of the session to make sure that the DELETE statements are actually send to the database.

    We're now left with the OrderLine entities that are new or modified. To make sure that they get persisted as well, I loop over the OrderLines collection and call SaveOrUpdate for every OrderLine instance.
    This will make sure that new OrderLines get inserted and modified ones, are updated.
    NHibernate will not update those OrderLines that are not changed.

Although this 'hack' is nicely hidden / abstracted by the Repository, I still find it a bit ugly, but at this very moment I see no better way to handle this kind of issue ...