How I made EF work more like an object database

I’m not a DB guru, so EF is usually very helpful for me. I like EF’s ORM capabilities, as this is quite a natural desire to want to map a set of in-memory objects to a database. This is all good, except when EF is a relational snob and I am an ignorant object-oriented programmer. The issue I refer to is that of “child orphans” in EF/DB speak.

The setup is this: I have a parent table/entity (let’s call it Person) and I have a child table/entity (let’s call it Pet). In my object oriented mindset a pet only exists in the context of a person – in other words you have to have a parent person object to have a pet (I don’t do strays a la Sochi).

public class Person
{
    public virtual int ID { get; set; }
    public virtual string Name { get; set; }
    public virtual ICollection<Pet> Pets { get; set; }
}

public class Pet
{
    public virtual int ID { get; set; }
    public virtual int PersonID { get; set; }
    public virtual string Name { get; set; }
}

And as you can tell from my object model, I didn’t want to fall into the normal EF code first style where child entities had navigation properties to their containing entity – that feels wrong to me (but I understand the need for a foreign key once mapped to the DB, thus the PersonID).

Anyway, to map this to the database I have a DbContext with a DbSet<Person>. I override OnModelCreating to overcome my lack of the navigation property I mentioned above:

public class PersonDatabase : DbContext
{
    public DbSet<Person> People { get; set; }

    protected override void OnModelCreating(DbModelBuilder modelBuilder)
    {
        modelBuilder.Entity<Person>().HasKey(x => x.ID);
        modelBuilder.Entity<Pet>().HasKey(x=>x.ID);
        modelBuilder.Entity<Person>().HasMany(x => x.Pets)
            .WithRequired().HasForeignKey(x => x.PersonID).WillCascadeOnDelete();
    }
}

And here’s an example of inserting into both tables:

using (var db = new PersonDatabase())
{
    var brock = db.People.Create();
    brock.Name = "Brock";
    var dog = new Pet();
    dog.Name = "Roo";
    brock.Pets.Add(dog);

    db.People.Add(brock);
    db.SaveChanges();
}

So notice no DbSet<Pet> is needed with EF in the DbContext. EF recognizes the relationship and will happily map any Pet in the Pets collection to a Pet table. I find this quite nice of EF and it plays right into my OO mindset. So naturally (or so I assumed) I thought I could remove a Pet from the collection of a Person and the Pet would be deleted from the table:

using (var db = new PersonDatabase())
{
    var brock = db.People.Single(x => x.Name == "brock");
    var roo = brock.Pets.Single(x => x.Name == "Roo");
    brock.Pets.Remove(roo);
    db.SaveChanges();
}

Not so. You get the exception:

Unhandled Exception: System.InvalidOperationException: The operation failed: The relationship could not be changed because one or more of the foreign-key properties is non-nullable. When a change is made to a relationship, the related foreign-key property is set to a null value. If the foreign-key does not support null values, a new relationship must be defined, the foreign-key property must be assigned another non-null value, or the unrelated object must be deleted.

It turns out that EF stops being about object relational mapping and decides to be a relational snob at this point. I am happy conceding that it’s right and knows more than me, but I just want my child row to be deleted. I don’t want to change my object model to support null FKs (since that’s not a correct mapping of my model). Also I’m quite confused because EF was kind enough to discern the Pets collection in Person it knew to automatically create the Pet table and inset rows when I added them to Person, but it doesn’t know to delete them when I remove them from the Person (especially when the foreign key is not null – I did that on purpose!). So I have a slight complaint that the behavior for adding doesn’t parallel the behavior for removing. Anyway, it knows best (I guess).

Turns out this behavior is nothing new and here’s the explanation: http://blog.oneunicorn.com/2012/06/02/deleting-orphans-with-entity-framework/

I was unsatisfied with this solution as it didn’t allow me to maintain my OO mindset when it came to EF (recall: it’s an object relational mapper). To solve my problem, I needed to be even more explicit (or deliberate) and use some interesting hooks in EF to tell it what I wanted it to do. It turns out there are enough events to let you know when an item is being removed from a collection, and in those event handlers you can mark and item as being removed to get the delete semantics I was looking for.

Here’s the insane code:

public class PersonDatabase : DbContext
{
    public PersonDatabase()
    {
        this.Set<Person>().Local.CollectionChanged +=
            delegate(object sender, NotifyCollectionChangedEventArgs e)
            {
                if (e.Action == NotifyCollectionChangedAction.Add)
                {
                    foreach (Person person in e.NewItems)
                    {
                        var entities = person.Pets as EntityCollection<Pet>;
                        if (entities != null)
                        {
                            entities.AssociationChanged += 
                                delegate(object sender2, CollectionChangeEventArgs e2)
                                {
                                    if (e2.Action == CollectionChangeAction.Remove)
                                    {
                                        var entity = e2.Element as Pet;
                                        if (entity != null)
                                        {
                                            this.Entry<Pet>(entity).State = EntityState.Deleted;
                                        }
                                    }
                                };
                        }
                    }
                }
            };
    }
}

So I read this as: any time a Person is materialized into the DbContext (either via inserting or loading) wire up to the Pets collection foreign key events. If that foreign key event is to remove the foreign key, then I want to remove the entity from the DB (which is the Pet). This gives me my OO semantics in a relational world. Too bad this isn’t easier in EF.

HTH