Many to Many delete cascade in NHibernate

Question

I have a scenario in a system which I've tried to simplify as best as I can. We have a table of (lets call them) artefacts, artefacts can be accessed by any number of security roles and security roles can access any number of artefacts. As such, we have 3 tables in the database - one describing artefacts, one describing roles and a many-to-many association table linking artefact ID to Role ID.

Domain wise, we have two classes - one for a role and one for an artefact. the artefact class has an IList property that returns a list of roles that can access it. (Roles however do not offer a property to get artefacts that can be accessed).

As such, the nhibernate mapping for artefact contains the following;

<bag name="AccessRoles" table="ArtefactAccess" order-by="RoleID" 
    lazy="true" access="field.camelcase-underscore" optimistic-lock="false">
    <key column="ArtefactID"/>
    <many-to-many class="Role" column="RoleID"/>
</bag>

This all works fine and if I delete an artefact, the association table is cleaned up appropriately and all references between the removed artefact and roles are removed (the role isn't deleted though, correctly - as we don't want orphans deleted).

The problem is - how to delete a role and have it clear up the association table automatically. If I presently try to delete a role, I get a reference constraint as there are still entries in the association table for the role. The only way to successfully delete a role is to query for all artefacts that link to that role, remove the role from the artefact's role collection, update the artefacts and then delete the role - not very efficient or nice, especially when in the un-simplified system, roles can be associated with any number of other tables/objects.

I need to be able to hint to NHibernate that I want this association table cleared whenever I delete a role - is this possible, and if so - how do I do it?

Thanks for any help.

Answer 1

You could make a mapping for the association table, and then call delete on that table where the Role_id is the value you are about to delete, and then perform the delete of the role itself. Should be fairly straightforward to do this.

Answer 2

Although I believe NHibernate must provide a way to do this without having the collection in the roles C# class, you can always set this behaviour in SQL. Select on cascade delete for the FK in the database and it should be automatic, just watch out for NHib's cache.

But I strongly advice you to use this as a last resource.

Answer 3

You need to create a mapping from Role to Artifact.

You can make it lazy-loading, and map it to a protected virtual member, so that it never actually gets accessed, but you need that mapping there for NHibernate to know that it has to delete the roles from the ArtefactAccess table

Answer 4

Thanks for the responses.

Elie, I wouldn't want the association table to be represented in the domain model and this would be the only way to manually delete against the association table in code using NHibernate.

David, adding the role -> artifact mapping though wouldn't delete the association data automatically as the inverse of that mapping owns the data - ie: removing a role from artifact, or deleting an artifact will clean up the association table rather than the other way around. So by adding the mapping, I'd have to again find all artefacts that have the role, remove the role and resave the artefacts (cleaning up the association table) and then remove the role - this just doesn't seem efficient to me.

Thanks, Tony

Answer 5

David,

I tried adding a bag from role up to artefact as a protected member, and as predicted, deleting the role wouldn't then clean up the association table. (Created bag mapping with an inverse="true" attribute).

If I take out the inverse = "true" attribute though, it does clean up the association table - but I'm led to believe that one side or the other should be inverse....

Cheers Tony

Answer 6

Since I was looking for this answer and found this thread on google (without an answer) I figured I'd post my solution to this. With three tables: Role, RolesToAccess(ManyToMany), Access.

Create the following mappings: Access:

<bag name="Roles" table="RolesToAccess" cascade="none" lazy="false">
      <key column="AccessId" />
      <many-to-many column="AccessId" class="Domain.Compound,Domain" />
    </bag>

<bag name="RolesToAccess" cascade="save-update" inverse="true" lazy="false">
      <key column="AccessId" on-delete="cascade" />
      <one-to-many class="Domain.RolesToAccess,Domain" />
    </bag>

Roles:

<bag name="Accesses" table="RolesToAccess" cascade="none" lazy="false">
      <key column="RoleId" />
      <many-to-many column="RoleId" class="Domain.Compound,Domain" />
    </bag>

<bag name="RolesToAccess" cascade="save-update" inverse="true" lazy="false">
      <key column="RoleId" on-delete="cascade" />
      <one-to-many class="Domain.RolesToAccess,Domain" />
    </bag>

As mentioned above you can make the RolesToAccess properties protected so they don't pollute your model.

Answer 7

Justin, How would you go about setting up that mapping that you gave, using annotations?