Fluent NHibernate: Issue with many-to-many relationship with custom intermediary table

Question

(see bottom for updates)

So, I have two objects (we'll call them ClassA and ClassB) that exhibit a many-to-many relationship (ClassA can have multiple ClassB objects and vice versa).

Rather than a traditional many-to-many, though, the actual "relationship" is, itself, an object, with different information about the link between the two other classes (relationship start date, relationship name, etc.)

So, rather than having this defined as a many-to-many between ClassA and ClassB, I have two one-to-many relationships defined (one on ClassA and on ClassB) pointed at a third, intermediary class (we'll call it "Relationship"). Structurally, I expect the tables to look like this:

     ClassA                      Relationship      
----------------              ----------------
      Id (PK)                       Id (PK)
     Name                          Name
  Description                    StartDate
                                 ClassA_Id (FK)
    ClassB                       ClassB_Id (FK)
---------------- 
      Id (PK)    
     Name        
  Description

To achieve this, I have set up the mapping like so (after a ton of trial and error):

public ClassAMap()
{
    Id(x => x.Id);
    Map(x => x.Name);
    HasMany(x => x.Relationship)    
        .KeyColumn("ClassA_Id")
        .ForeignKeyConstraintName("FK_Relationship_ClassA")
        .LazyLoad()
        .Cascade.All()
        .Inverse();
}

public ClassBMap()
{
    Id(x => x.Id);
    Map(x => x.Name);
    HasMany(x => x.Relationship)    
        .KeyColumn("ClassB_Id")        
        .ForeignKeyConstraintName("FK_Relationship_ClassB")
        .LazyLoad()
        .Cascade.All()
        .Inverse();
}

public RelationshipMap()
{
    Id(x => x.Id);
    Map(x => x.CreatedDate);
    References(x => x.ClassAObject)
        .Column("ClassA_Id")
        .Cascade.SaveUpdate();
    References(x => x.ClassBObject)
        .Column("ClassB_Id")
        .Cascade.SaveUpdate();
}

Now, for adding/deleting parent objects, his works exactly as expected. For example, if I create a new ClassA and ClassB, link them trough a Relationship and then Save ClassA, then all of the associated records are added to the three tables. Similarly, if I remove that same ClassA, then the ClassA record and the Relationship records are removed, but the ClassB record remains (expected behavior).

If, however, I just try to remove the Relationship from one of the parent objects (e.g. classA.Relationships.Remove(relationshipObject); classARepository.Update(classA);), then I get errors similar to the following:

NHibernate.StaleObjectStateException: Row was updated or deleted by another transaction (or unsaved-value mapping was incorrect): [ClassB]

NHibernate.ObjectDeletedException: deleted object would be re-saved by cascade (remove deleted object from associations) [Relationship#9]

Additionally, if I completely reload the ClassA object from the database and remove a membership as described above, then it executes without error, but my Relationship is not removed from the database.

How can I alter the mapping to achieve the behavior that I an looking for?

For reference, here's an outline of the scenarios that must pass:

1. Add new ClassA with Relationship
   • Records for ClassA/B/Relationship added or updated
2. Remove existing ClassA
   • Records for ClassA and Relationships deleted
   • ClassB records remain in table
3. Remove Relationship from ClassA.Relationships list; update ClassA
   • Remove Relationship from Relationships table
   • ClassA and ClassB records remain

UPDATE: Based on @JamieIde's answer, I came up with the following adjustments:

• Null out the references to ClassA and ClassB on the Relationship:

// NOTE, this is an example snippet to show the steps that need to be taken

using(var session = SomeSessionFactory().OpenSession())
{
    using (var transaction = session.BeginTransaction())
    {
        // preferably place these four lines in a public method on one of the objects
        classAObject.Relationships.Remove(relationship);
        classBObject.Relationships.Remove(relationship);
        relationship.ClassA = null;
        relationship.ClassB = null;

        transaction.Commit();
    }
}   

• Change .Cascade.All() on the ClassA/ClassB to Cascade.AllDeleteOrphan(). This prevents NHibernate from inserting blank records into the ClassA and ClassB tables after nulling out the references on the Relationship object

public ClassAMap()
{
    Id(x => x.Id);
    Map(x => x.Name);
    HasMany(x => x.Relationship)    
        .KeyColumn("ClassA_Id")
        .ForeignKeyConstraintName("FK_Relationship_ClassA")
        .LazyLoad()
        .Cascade.AllDeleteOrphan() // here's the key line
        .Inverse();
}

Answer 1

You don't need to alter the mapping, you need to null the reference to the one side in the Relationship object.

classA.Relationships.Remove(relationshipObject);
relationshipObject.ClassAObject = null;
session.Flush();

Basically you need to be careful to maintain both sides of the relationship so that the in-memory object graph is correct and can be persisted by NHibernate. A common practice is to encapsulate this:

void RemoveRelationship(RelationshipObject relationshipObject)
{
    Relationships.Remove(relationshipObject);
    relationshipObject.ClassAObject = null;
}

Also, it's unlikely that you need to call Update, just flush the session or (better yet) commit the transaction.