NHibernate - Set foreign key column to null when deleting

Question

I have two entities, EntityA and EntityB. EntityB can optionally be related to EntityA, and has a foreign key column that is nullable. When EntityA is deleted, I want the foreign key in EntityB to be set to null.

So when I delete EntityA, I'd like it to set the foreign key in any referenced EntityBs before issuing the DELETE command. I've seen NHibernate do this before "accidentally", and now that I actually want it to do that, I can't seem to get it right. The best I've been able to do is delete EntityB once it is orphaned, which is not what I want. Of course, I could set the foreign key constraint in SQL Server to set the column to null, but I feel like NHibernate should be able to do this. Similarly, I could loop through all EntityB referenced by the EntityA to be deleted, and set them to null. Again, why should I have to?

Here are my entities and mappings:

public class EntityA : Entity // Entity contains the ID property and implements GetHashCode() and Equals()
{
    // irrelevant properties

    public virtual IList<EntityB> EntityBs { get; protected set; }
}

public class EntityB : Entity
{
    // irrelevant properties
    public virtual EntityA EntityA { get; set; }
}


public class EntityAMap: ClassMap<EntityA>
{
    public EntityAMap()
    {
         HasMany(c => c.EntityBs)
             .ForeignKeyConstraintName("FK_EntityA_EntityB")
         .KeyColumn("EntityA_Id")
             .Inverse()
             .Cascade.None(); // i've tried other cascade settings with little luck
    }   
}

public class EntityBMap: ClassMap<EntityB>
{
    public EntityBMap()
    {
         References(c => c.EntityA)
             .Nullable()
             .Column("EntityA_Id")
             .ForeignKey("FK_EntityA_EntityB")
             .Index("IX_EntityB_EntityA_Id")
             .Cascade.None();
    }   
}

I then delete like this:

var entityA = Repository.Get(idToDelete);
// entityA.EntityBs.Clear(); // tried this, no affect
Repository.Remove(entityA);

Repository.Flush(); // is it possible this isn't working because I'm missing a Tx?

The result is a foreign key constraint violation, because EntityB.EntityA_Id is not being nulled out.

Have you tried setting the hasmany to KeyNullable().Cascade.All()? — dotjoe

Fourth Fourth · Accepted Answer · 2011-06-10T20:07:06

You need to do something like:

entityA.EntityBs.ToList().ForEach(b => b.EntityA = null );

Repository.Remove(entityA);
Repository.Flush();

If any B's are still referencing A you can't delete A, especially with no cascading going on.

NHibernate - Set foreign key column to null when deleting

2 Answers