Forcing NHibernate to cascade delete before inserts

Question

I have a parent object which has a one-to-many relationship with an ISet of child objects. The child objects have a unique constraint (PageNum and ContentID - the foreign key to the parent).

<set name="Pages" inverse="true" cascade="all-delete-orphan" access="field.camelcase-underscore">
    <key column="ContentId" />
    <one-to-many class="DeveloperFusion.Domain.Entities.ContentPage, DeveloperFusion.Domain, Version=1.0.0.0, Culture=neutral, PublicKeyToken=null" />
</set>

The problem I've hit is if I remove a ContentPage element from the parent collection, and then add a new one with the same unique key within the same transaction... You get a unique constraint violation because NHibernate attempts to perform the insert before the delete.

Is there a way to force NHibernate to perform the delete first?

Pointtopoint

Answer 1

There is no option to specify the order of operations in a transaction as it is hard-coded as follows (from the documentation):

The SQL statements are issued in the following order

• all entity insertions, in the same order the corresponding objects were saved using ISession.Save()
• all entity updates
• all collection deletions
• all collection element deletions, updates and insertions
• all collection insertions
• all entity deletions, in the same order the corresponding objects were deleted using ISession.Delete()

(An exception is that objects using native ID generation are inserted when they are saved.)

As such, can I challenge you to answer why you are adding a new entity with an existing identifier? An identifier is supposed to be unique to a specific "entity." If that entity is gone, so should be its identifier.

Another option would be to do an update on that record instead of a delete/insert. This keeps the ID the same so there is no unique constraint violation (on the key at least) and you can change all the other data so that it's a "new" record.

EDIT: So apparently I wasn't entirely paying attention to the question when I responded since this is a problem with a unique constraint on a non-primary-key column.

I think you have two solutions to choose from:

1. Call Session.Flush() after your delete which will execute all the changes to the session up to that point, after which you can continue with the rest (inserting your new object). This works inside of a transaction as well so you don't need to worry about atomicity.
2. Create a ReplacePage function which updates the existing entity with new data but keeps the primary-key and the unique column the same.

Windsucking

Hi Stuart, each entity does have a unique primary key identifier which is not re-used. however, it also has a unique constraint on two other columns (contentID - the foreign key to its parent, and pageNum - you can only have one page with the same pagenum). Its that which is causing the problem. – Pointtopoint

I have a similar problem as well. I've been contemplating removing the unique constraint, but I really don't like that solution. – Integrity

Sorry James, I didn't read closely enough. See if the Session.Flush() method works for you. – Windsucking

I don't know why, but I have a collection which does not do collection deletions first and inserts afterwards. I have a unique index on the table from the collection and it raises an error because the insertions are done before the deletions. What gives? – Oyster

@StuartChilds: 2nd solution is painful for me: I have an entity that has a collection of entities, that has a collection of entities (A-*B-*C). When I solve the problem for A-*B relation, it goes deeper into B-*C relation. – Sherise

@StuartChilds +1 for doing an update if it's really the same entity (vs a delete/re-insert). I got burned by this too, once I switched to an update, all is well. – Ous

right, but the "hard" part is to convert from delete/insert to update because you have to do it manually for each transaction with similar characteristics ... My case is where I have the table ProcessRole and I delete then add the "same" role+user combination but both operations are applied to the database in a Repo.Update(process) – Palgrave

Using 'Session.Flush()' inside the transaction was the solution for my problem! I was looking a lot for "NHibernate query execution order" and "Nhibernate queries order of execution" - got to this answer with some other efforts.. just pointing it out in case someone else is looking for it. – Dactylography

Answer 2

I am experiencing the same problem ... I have an entity which has a collection that is mapped to a table which contains a unique constraint.

What I do not understand is, that according to Stuarts reply, collection deletions should occur before collection inserts? When I dive into the NHibernate sourcecode, I find the CollectionUpdateAction class, which contains this code in its Execute method:

persister.DeleteRows(collection, id, session);
persister.UpdateRows(collection, id, session);
persister.InsertRows(collection, id, session);

Then, I would assume that deletes are executed before inserts, but apparently this is not the case. Is the CollectionUpdateAction not used in this scenario? When is the CollectionUpdateAction used?

Answer:

I have worked around this like this:

• In my mapping, I've set the cascade option to 'delete-orphan' instead of 'all-delete-orphan'

• All my database access goes via a repository; in the save method of my repository, I have this code for my entity:

  public void Save( Order orderObj )
  {
      // Only starts a transaction when there is no transaction
      // associated yet with the session
     With.Transaction(session, delegate()
     {
         session.SaveOrUpdate (orderObj);
         session.Flush();
  
         foreach( OrderLine line in orderObj.Lines )
         {
              session.SaveOrUpdate (line);
         }
      };
   }
  

So, I save the orderObj, and because the cascade is set to delete-orphan, objects that are to be deleted, will be deleted from the database.

After I call SaveOrUpdate, I must make sure to flush the changes to the database.

Since the delete-orphan cascade setting makes sure that no OrderLines are inserted or updated, I have to loop over my collection, and call 'SaveOrUpdate' for each OrderLine. This will make sure that new OrderLines will be inserted, and modified ones are updated. No action will be performed for OrderLines that have not changed.

Although it is not the ideal solution (it's an ugly hack IMHO), it kind of works, and it is abstracted away behind the repository, so this is how I deal with this problem for now...

Causeway

hi Frederik, this solution has worked well for me, but now I'm getting "object references an unsaved transient instance" exceptions on the session.Flush() line - having set the cascade to delete-orphan. I'm not sure what's changed though!! Have you come across this before? James – Pointtopoint

No, I haven't had this issue. Are you sure that you call 'Save' or SaveOrUpdate on each item in the set your self ? – Causeway

yeah, I am - but that happens after the call to Flush() right? and I'm getting an error before - it doesn't seem to like the unsaved objects sitting there when it flushes? (even though I plan to save later?) – Pointtopoint

You have to set your Flushmode to never, and, call 'flush' yourself explicitly ... Offcourse, this can be a big drawback. – Causeway

Add Session.Flush() solved my problem. If any collection updated, Remove-Add etc... you should update immediately parent object(with flush method). ParenObject.Collection.Remove(any); Repository.Update(ParentObject);ParenObject.Collection.Add(any);Repository.Update(ParentObject) – Sparrow

Answer 3

If you get rid of the child primary key (ContentPage.Id) and make the composite key the primary key (ie PageNum and ContentID), then I believe it may work. This is a solution offered to the same issue occurring in hibernate.

Portland

Composite keys have unpleasant side effect: with each level of tables hierarchy (A->B->C) the amount of columns is growing. – Sherise