fluent-nhibernate cascade delete to collection of joined-subclassess

Question

New user to fluent nhibernate here and I have a model with a parent class that holds a collection of subclassed children (using TablePerSubClass inheritance). When attempting to to test cascading deletes by calling delete only on the parent object I get a constraint error between the child table and its joined-subclass table. To demonstrate...

    public class ParentMap : ClassMap<Parent> {
        public ParentMap() {
            Id(x => x.Id);
            HasMany(x => x.Children)
                .Cascade.AllDeleteOrphan()
                .ForeignKeyCascadeOnDelete()
                .Inverse();
        }
    }

    public class ChildMap : ClassMap<Child> {
        public ChildMap() {
            Id(x => x.Id);
            References(x => x.Parent).Not.Nullable();
        }
    }

    public class ExtendedChildMap : SubclassMap<ExtendedChild> {
        public ExtendedChildMap() {
            Map(x => x.extraFeature);
        }
    }

When unit testing with the following code...

using (var session = sessionFactory.OpenSession()) {
    using (var transaction = session.BeginTransaction()) {

        var p = new Parent();
        var c1 = new Child() { Parent = p };
        var c2 = new ExtendedChild() { Parent = p };

        session.SaveOrUpdate(p);
        session.SaveOrUpdate(c1);
        session.SaveOrUpdate(c2);
        Assert.IsTrue(session.Query<Parent>().Count() == 1);
        Assert.IsTrue(session.Query<Child>().Count() == 2);
        Assert.IsTrue(session.Query<ExtendedChild>().Count() == 1);

        session.Delete(p);
        Assert.IsTrue(session.Query<Parent>().Count() == 0);
    }
}

The test fails on the final assertion with

The DELETE statement conflicted with the REFERENCE constraint "FKDB46742824B330ED". The conflict occurred in database "testDB", table "dbo.ExtendedChild", column 'Child_id'

If the collection only holds the baseclass Child objects it works as expected, but when a derived ExtendedChild is added the delete doesn't seem to propogate to the baseclass. Feel like I'm missing something obvious here but I've still not managed to solve this after a good while searching.

Lastly, I'm also still not 100% clear on the functional differences between Cascade.AllDeleteOrphan and ForeignKeyCascadeOnDelete... or more precisely (ignoring the save/update part) what is the case that the former doesn't handle and requires the latter to be specified?

Answer 1

The problem with reference constraint is related to the fact, that we do not properly set the parent-child relation.

I. Not only child should/must know about the parent, but also parent must be informed about its children:

var p = new Parent();
// assign parent to children
var c1 = new Child() { Parent = p };
var c2 = new ExtendedChild() { Parent = p };
// assign children to parent as well
p.Children.Add(c1);
p.Children.Add(c2);

The above code and the mapping with cascade is enough for NHibernate to call just:

session.SaveOrUpdate(p);

and Parent p, Child c1 and ExtendedChild c2 ... all will be persisted.

And if we are inside of one continues session, the existing instance of parent p, can be deleted:

session.Delete(p) 

and that will also trigger deletion of all the children - because parent does know about them: and call the cascade for them...

II. The cascading must be driven by NHibernate only - not by DB.

We also have to remove the ForeignKeyCascadeOnDelete which effectively does create the DB script with a SQL Server native cascading... this is not needed - in this case.

HasMany(x => x.Children)
    ...
    // good and very helpful setting Cascade
    .Cascade.AllDeleteOrphan()

    // this is not what we want
    // we do not need cascade on SQL Server side
    // .ForeignKeyCascadeOnDelete()

Unit testing:

The best way, how to make the above test fully working is to split creation and deletion. To do that, we should issue:

• Flush() to be sure that the session does persist all the changes
• Clear() to start with a new clean table

the structure could look like this:

// as above... create Parent and Children - assign each to other
// and call parent to save
session.SaveOrUpdate(p);

// now we have to be sure, that the session will propagate 
// all the changes into the DB
session.Flush();

// and reset all the settings:
session.Clear();

// now (re)load the parent to later let NHiberante do the correct cascade
var parentReloaded = session.Get<Parent>(p.Id);

// Delete all the related stuff
session.Delete(parentReloaded);

In case, that the constraint is related to our newly created children, this will do the job....