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’ve tried adding a protected IList bag to role that links up to artefact’s (even though I don’t want this in my domain model) and set this as the inverse relationship (inverse=”true” on the bag) but this doesn’t solve the problem.

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.