Fluent NHibernate only cascade delete of association record

Question

We're using NHibernate for our membership system. A User can be a member of many Roles and a Role can have many users.

When a Role or a User is deleted, it should only cascade the delete of the association record ("RoleUsers" table).

Deleting a Role works as expected. However, deleting a User does not delete the association record and as such fails due to a foreign key constraint.

My mapping on the Role side:

        HasManyToMany(r => r.Users)
            .Access.CamelCaseField()
            .Table("RoleUsers")
            .ParentKeyColumn("RoleId")
            .ChildKeyColumn("UserId")
            .AsSet();

Mapping on the User side:

        HasManyToMany(u => u.Roles)
            .Access.CamelCaseField()
            .Table("RoleUsers")
            .ParentKeyColumn("UserId")
            .ChildKeyColumn("RoleId")
            .Inverse(); // we'll add user to role, not role to user

And the failing test:

    [Test]
    public void Deleting_user_should_not_delete_roles()
    {
        var user = new User("[email protected]", "John", "Doe", "Secr3t");
        var role = new Role("Admin");
        role.AddUser(user);

        object id;
        using (var txn = Session.BeginTransaction())
        {
            id = Session.Save(user);
            Session.Save(role);
            txn.Commit();
        }

        Session.Clear();

        var fromDb = Session.Get<User>(id);

        using (var txn = Session.BeginTransaction())
        {
            Session.Delete(fromDb);
            txn.Commit();
        }

        Session.Query<Role>().Count().ShouldEqual(1);
    }

I've tried every combination of Cascade on the user mapping and it either fails or deletes the association record AND the role (not what I want).

Radim Köhler Radim Köhler · Accepted Answer · 2012-12-07T08:19:48

Inverse and cascading are two different concepts. And of course, both are supported on <many-to-many> relation. See the documentation 6.8 (scroll down almost to 6.9)

http://nhibernate.info/doc/nh/en/index.html#collections-bidirectional

1) Firstly, we can remove the inverse setting of the User.Roles collection. This will straightens the behavior of the Users`s relations to Roles, and force their deletion before User itself is deleted.

2) Secondly. If the Roles collection of the User is marked as inverse,

HasManyToMany(u => u.Roles)
  ...
  .Inverse(); // we'll add user to role, not role to user

deletion of any User, will never trigger deletion of the pair. That's because we are explicitly saying: the one and only one who care about the relation is the Role.

So if we would like to continue in your scenario:

.Inverse(); // we'll add user to role, not role to user

we should be consitent. "we'll remove user from roles, not roles from user":

[Test]
public void Deleting_user_should_not_delete_roles()
{
  var user = new User("[email protected]", "John", "Doe", "Secr3t");
  var role = new Role("Admin");
  role.AddUser(user);

  object roleId;
  object id;
  using (var txn = Session.BeginTransaction())
  {
    id = Session.Save(user);
    roleId = Session.Save(role);
    txn.Commit();
  }

  Session.Clear();

  // take both from DB
  var userFromDb = Session.Get<User>(id);
  var roleFromDb = Session.Get<Role>(roleId);

  using (var txn = Session.BeginTransaction())
  {
     // HERE just remove the user from collection
     roleFromDb.Users.Remove(userFromDb);

     // all relations will be deleted
     Session.Save(roleFromDb);
     txn.Commit();
  }
  ... 
  // assertions
  // "John's" relation to Role "admin" is deleted
}

NOTE: 3) Cascade was not used in there, but could help to reduce Session.Save(user)...

EDIT: Extending the point 3)

Deletion of the user as Ben Foster noticed in a comment.

3) We should even allow the Role to manage its Users collection completely. Let's introduce the casdace="all" (casdace="all-delete-orhpan" if User without any Role should be deleted at all). Now we can add/update users only via Role object.

the mapping of the Role's Users collection should look like:

HasManyToMany(r => r.Users)
  .Access.CamelCaseField()
  .Table("RoleUsers")
  .ParentKeyColumn("RoleId")
  .ChildKeyColumn("UserId")
  //.Cascade.All(); // just save or update instance of users
  .Cascade.AllDeleteOrphan(); // will even delete User without any Role
  .AsSet();

Having inverse and cascade we can adjust the test:

[Test]
public void Deleting_user_should_not_delete_roles()
{
  var user = new User("[email protected]", "John", "Doe", "Secr3t");
  var role = new Role("Admin");
  role.AddUser(user);

  object roleId;
  using (var txn = Session.BeginTransaction())
  {
     // I. no need to save user
     roleId = Session.Save(role);
     ...

And later call this to get rid of a User

...
using (var txn = Session.BeginTransaction())
{
  var user = Session.Get<User>(id);
  var roles = user.Roles.ToList();
  roles.ForEach(role => role.RemoveUser(user))
  // II. not only relations, but even the User is deleted
  // becuase updated roles triggered delete orhpan
  // (no Session.Update() call there)
  txn.Commit();
}

Fluent NHibernate only cascade delete of association record

1 Answers

EDIT: Extending the point 3)