Skip to content

How I made EF work more like an object database

March 30, 2014

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

11 Comments leave one →
  1. March 30, 2014 1:26 pm

    That’s a pretty nice way to have trigger-like functionality in EF. Not sure if I’d use that technique for anything else rather than deletion of child objects.

  2. A_li_N permalink
    March 30, 2014 1:48 pm

    This is a serious question: why, in an object relation, would you not want access to the parent object? A person has a list of pets and pets belong to a person. With your model, you would not be able to, for example, find a pet on the side of a road without first knowing its owner. A gaming example would be having a pet take damage… You would first have to look up its owner to find the pet in question, which would be an extra unneeded step.

    • Mercury permalink
      April 18, 2014 11:01 am

      It depends on the application. If you’re representing actual physical animals, then you likely need direct access to them, and pets can exist without owners. In that case, this code doesn’t make sense, as it should be possible to remove the link between a pet and an owner while still wanting both of them to exist.

      On the other hand, if you’re just a system for tracking information about people that happens to include a list of their pets, then outside of the possibility of pets shared between multiple people, removing a pet from a person should delete the pet. Addresses or phone numbers would be a very similar case, and the same sharing considerations would apply.

      It’s a question of references; if nothing refers to an object, we expect it to be deleted. Similarly, in EF, if nothing refers to an entity, we can reasonably expect it to be deleted. I imagine the reason EF doesn’t contain this functionality by default is because it can’t know that we don’t care about having a top-level reference to the entity in question. Even if we don’t have one in code, we might be accessing the database from a different context or a different system entirely that EF doesn’t know about, and it can’t reasonably assume what records we do and don’t want.

      Conceivably, some sort of attribute system could solve this problem, but it’s still tricky. If you flag Pet as “Delete me if there aren’t any references to me!” then, given multiple data contexts and that EF simply does not know what references there may or may not be outside of its own, it can’t realistically determine if and when a pet has no references to it. Maybe something like “this entity exists solely in the context of this other entity” would work.

  3. kenvangilbergen permalink
    April 1, 2014 1:52 am

    Relational snob :) that just made my day.

  4. red2nb permalink
    June 24, 2014 6:57 am

    I’m happy to see that I’m not the only one who enjoys to have real OOP domain models. I wasn’t satistified with the approaches consisting in having specialized repositories to handle the deletion logic because they lead to break the encapsulation principle by putting some of the domain logic in the external world.

    Because what about if I want to add some business rules to the deletion logic ? For instance, a person may have to sell the pet before being able to delete it. In the “best” case this would be handle with a PetRepository which would correct the orphans behavior but I don’t like this solution because it would lead to have 1 repository per object type. I prefer to use 1 Generic Repository and to only expose the repositories of the Root Aggregates.

    I have also seen another solution which consists in making the Foreign Key part of the Primary Key. For example, if the “PersonID” is part of your Pets primary key as well as “ID”, Entity Framework would handle the orphans deleting well. I’m using this approach… and it literally messed up my mapping code and made the relationships incredibly hard to understand. Because then, every time you want to have a foreign key to an object having such a composite primary key, the foreign key has to be composite. And you have think about cascade deleting etc… So the bonus was that I’ve got to add senseless Foreign Keys in my models. I ended up with a hard mapping code and messed up models.

    Your code looks definitely ugly but is actually easy to understand. I’ll move to this approach immediately since it has the advantage of keeping the domain models, the “OO mindset” and the mapping relationship clean.

    Thanks !

  5. July 2, 2014 7:46 pm

    I tend to logical delete everything. :D I don’t have a single ‘real database delete’ on my code.

  6. Jonathan Choy permalink
    July 3, 2014 7:23 pm

    So, this seems like a good candidate for abstraction, to wit:

    public LodgeChroniclerContext()
    : base(“Name=LodgeChronicler”)
    {
    this.AddMaterializedEntityHandler(p => p.Characters);
    }

    private void AddMaterializedEntityHandler(Func<TEntity, ICollection> removeTrackingSelector)
    where TEntity : class
    where TChild : class
    {
    this.Set().Local.CollectionChanged += (_, e) =>
    {
    if (e.Action == NotifyCollectionChangedAction.Add)
    {
    foreach (TEntity item in e.NewItems)
    {
    var entity = item;

    var children = removeTrackingSelector.Invoke(entity) as EntityCollection;
    if (children != null)
    {
    children.AssociationChanged += (__, e1) =>
    {
    if (e1.Action == CollectionChangeAction.Remove)
    {
    var child = e1.Element as TChild;
    if (child != null)
    {
    this.Entry(entity).State = EntityState.Deleted;
    }
    }
    };
    }
    }
    }
    };
    }

  7. July 31, 2014 6:33 am

    I used Jonathan Choy’s generic solution but I’m not sure either of them work with proxies and lazy-loaded collections because the children list is always null. If I look at the parent entity in the debugger I can see that at the point that the parent is added to the DbSet, the child collection is not materialized. Maybe it only works with eager loading?

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: