What is the correct way to define many-to-many relationships in NHibernate to allow deletes but avoiding duplicate records

Question

I've been fighting with an NHibernate set-up for a few days now and just can't figure out the correct way to set out my mapping so it works like I'd expect it to.

There's a bit of code to go through before I get to the problems, so apologies in advance for the extra reading.

The setup is pretty simple at the moment, with just these tables:

Category
CategoryId
Name

Item
ItemId
Name

ItemCategory
ItemId
CategoryId

An item can be in many categories and each category can have many items (simple many-to-many relationship).

I have my mapping set out as:

<hibernate-mapping xmlns="urn:nhibernate-mapping-2.2"
                   assembly="..."
                   namespace="...">

  <class name="Category" lazy="true">

    <id name="CategoryId" unsaved-value="0">
      <generator class="native" />
    </id>
    <property name="Name" />

    <bag name="Items" table="ItemCategory" cascade="save-update" inverse="true" generic="true">
      <key column="CategoryId"></key>
      <many-to-many class="Item" column="ItemId"></many-to-many>
    </bag>

  </class>

</hibernate-mapping>

<hibernate-mapping xmlns="urn:nhibernate-mapping-2.2"
                   assembly="..."
                   namespace="...">

  <class name="Item" table="Item" lazy="true">

    <id name="ItemId" unsaved-value="0">
      <generator class="native" />
    </id>
    <property name="Name" />

    <bag name="Categories" table="ItemCategory" cascade="save-update" generic="true">
      <key column="ItemId"></key>
      <many-to-many class="Category" column="CategoryId"></many-to-many>
    </bag>

  </class>

</hibernate-mapping>

My methods for adding items to the Item list in Category and Category list in Item set both sides of the relationship.

In Item:

    public virtual IList<Category> Categories { get; protected set; }
    public virtual void AddToCategory(Category category)
    {
        if (Categories == null)
            Categories = new List<Category>();

        if (!Categories.Contains(category))
        {
            Categories.Add(category);
            category.AddItem(this);
        }
    }

In Category:

    public virtual IList<Item> Items { get; protected set; }
    public virtual void AddItem(Item item)
    {
        if (Items == null)
            Items = new List<Item>();

        if (!Items.Contains(item))
        {
            Items.Add(item);
            item.AddToCategory(this);
        }
    }

Now that's out of the way, the issues I'm having are:

1. If I remove the 'inverse="true"' from the Category.Items mapping, I get duplicate entries in the lookup ItemCategory table.

2. When using 'inverse="true"', I get an error when I try to delete a category as NHibernate doesn't delete the matching record from the lookup table, so fails due to the foreign key constraint.

3. If I set cascade="all" on the bags, I can delete without error but deleting a Category also deletes all Items in that category.

Is there some fundamental problem with the way I have my mapping set up to allow the many-to-many mapping to work as you would expect?

By 'the way you would expect', I mean that deletes won't delete anything more than the item being deleted and the corresponding lookup values (leaving the item on the other end of the relationship unaffected) and updates to either collection will update the lookup table with correct and non-duplicate values.

Any suggestions would be highly appreciated.

Answer 1

What you need to do in order to have your mappings work as you would expect them to, is to move the inverse="true" from the Category.Items collection to the Item.Categories collection. By doing that you will make NHibernate understand which one is the owning side of the association and that would be the "Category" side.

If you do that, by deleting a Category object it would delete the matching record from the lookup table as you want it to as it is allowed to do so because it is the owning side of the association.

In order to NOT delete the Items that are assigned to a Category object that is to be deleted you need to leave have the cascade attribe as: cascade="save-update".

cascade="all" will delete the items that are associated with the deleted Category object.

A side effect though would be that deleting the entity on the side where the inverse=tru exists will thow a foreign key violation exception as the entry in the association table is not cleared.

A solution that will have your mappings work exactly as you want them to work (by the description you provided in your question) would be to explicitly map the association table. Your mappings should look like that:

<hibernate-mapping xmlns="urn:nhibernate-mapping-2.2"
                   assembly="..."
                   namespace="...">

  <class name="Category" lazy="true">

    <id name="CategoryId" unsaved-value="0">
      <generator class="native" />
    </id>
    <property name="Name" />

    <bag name="ItemCategories" generic="true" inverse="true" lazy="true" cascade="none">
        <key column="CategoryId"/>
        <one-to-many class="ItemCategory"/>
    </bag>

    <bag name="Items" table="ItemCategory" cascade="save-update" generic="true">
      <key column="CategoryId"></key>
      <many-to-many class="Item" column="ItemId"></many-to-many>
    </bag>

  </class>

</hibernate-mapping>

<hibernate-mapping xmlns="urn:nhibernate-mapping-2.2"
                   assembly="..."
                   namespace="...">

  <class name="Item" table="Item" lazy="true">

    <id name="ItemId" unsaved-value="0">
      <generator class="native" />
    </id>
    <property name="Name" />

    <bag name="ItemCategories" generic="true" inverse="true" lazy="true" cascade="all-delete-orphan">
        <key column="ItemId"/>
    <one-to-many class="ItemCategory"/>
    </bag>

    <bag name="Categories" table="ItemCategory" inverse="true" cascade="save-update" generic="true">
      <key column="ItemId"></key>
      <many-to-many class="Category" column="CategoryId"></many-to-many>
    </bag>

  </class>

</hibernate-mapping>

As it is above it allows you the following:

1. Delete a Category and only delete the entry in the association table without deleting any of the Items
2. Delete an Item and only delete the entry in the association table without deleting any of the Categories
3. Save with Cascades from only the Category side by populating the Category.Items collection and saving the Category.
4. Since the inverse=true is necessary in the Item.Categories there isn't a way to do cascading save from this side. By populating the Item.Categories collection and then saving the Item objec you will get an insert to the Item table and an insert to the Category table but no insert to the association table. I guess this is how NHibernate works and I haven't yet found a way around it.

All the above are tested with unit tests. You will need to create the ItemCategory class mapping file and class for the above to work.