Handling child collectins in Entity Framework Code First

There are a lot of blog posts out there on the issue of removing items from child collections in Entity Framework.

The Problem

The Problem is, that when you remove an item from a child collection of an entity, EF just sets the foreign key null in the child collections table. It does not delete the item. While this means that the item does no longer appear in your collection, it is not a satisfying situation as it leaves orphaned records around.

Possible solutions and problems with the solutions

There are three possible solutions to the problem:

1. Explicitly remove the child
2. Identifying Relationships
3. SaveChanges

The first one means, that whenever you remove something from a child collection you remove it from the according DbSet-Property of your context as well. This is obviously a bad design, becaus for child collection I do not want a DbSet collection of its own.

The second one means that you need to make the primary key of the parent part of the primary key of the child. This is bad because I need to change my model and bloat it with unnecessary properties - even worse these properties merely contain technical database stuff.  

The third one comes close to a good solution, but not the way I like to handle it. It requires to override the SaveChanges method of the context and handle deleting orphans there. This is the best solution so far because it tackles the problem close to its origin: inside the technical EF code stuff. But almost any implementations on the web tend to do it in a way that comes close to solution 2: the have kind of a navigation propertey in the child that points to the parent and that can be checked for null. Others suggest using domain events, which is generally a great concept but it feels weired to introduce it to solve a infrastructural problem.

My context

So here is my context, the scenario was from a coding dojo we did. It is a very database focused and very simplified implementation of a shopping cart:

public class Basket
{
    public Guid Id { get; set; }
    public Guid CustomerId { get; set; }
    public virtual IList<BasketItem> Positions { get; set; }
}

public class BasketItem
{
    public int Id { get; set; }
    public string ArticleName { get; set; }
    public decimal Amount { get; set; }
    public decimal Price { get; set; }
}

public class BasketContext : DbContext
{
    public BasketContext() : base("BasketContext") 
    {
    }
    
    public DbSet<Basket> Baskets { get; set; }
}

That is it. You see that Basket has a Guid-Id while BasketItem has an int-Id. This reflects the fact that I consider Basket to be an Aggregate Root on the level of my domain model, while Basket Item is just a contained entity, that does not have an id that is relevant outside of its containing basket. The need for the id is just for the sake of a relational database.
So, when searching for a solution to my problem, I made the following premise, stated loud and clear:

"I will try as hard as I can (very very hard) to never change my model just for the sake of a relational database!"

Having said that, all solutions I found on the web are not for me. Because as my requirements were, my model has no need for a Basket property, or even worse a BasketId property, on the BasketItem. This renders the above options 2 and 3 useless. Option 1 is useless as it requires me to add a DbSet for BasketItems, which is not necessary as they dont get queried directly.

My (prototype) solution

The following solution is not production ready. It is the solution I found in a late-at-the-day hacking session after the dojo that brought the problem to the surface. It ignores edge cases. It is not tested well. Keep that in mind, and dont say I did not warn you.

The solution follows pattern number three from the above solutions, overriding SaveChanges(). But it does not require to fiddle around with your model. My idea was: if EF knows that it has to set a value in table to null, it must be able to find out for me as well.

public override int SaveChanges()
{
    var objectContext = ((IObjectContextAdapter) this).ObjectContext;
    
    objectContext.DetectChanges();

    var deletedThings =
        objectContext.ObjectStateManager.GetObjectStateEntries(EntityState.Deleted).ToList();

   
    foreach (var  deletedThing in deletedThings)
    {
        if (deletedThing.IsRelationship)
        {
            var deletedKey = deletedThing.OriginalValues[1] as EntityKey;
            var entityToDelete = objectContext.GetObjectByKey(deletedKey);
            objectContext.DeleteObject(entityToDelete);
        }
    }

    return base.SaveChanges();
}

This can not be done using the DbContext-Api, but needs to be done with the ObjectContext-Api, so we have to obtain it using the IObjectContextAdapter interface. We need to call DetectChanges, otherwise the deletedThings are empty. In case of an orphaned child entry, the deletedThings contain entries for relationships (deletedThing.IsRelationship yields true). In that case we can find the ends of the relationship in the OriginalValues. A two element array where index 0 points to the parent (the basekt in the example) and index 1 points to the child (the BasketItem). By "points to" I mean that the OriginalValues contains an EntityKey-object identifying the object in question. So using GetObjectByKey(deletedKey) we can load the orpahned child. We must delete it using DeleteObject(entityToDelete) because there is no explicit EntitySet holding it.

I hope someone might find it useful.

Manage Views with EF Code First + Migration Support

I had to search around for a while to find a solution to the problem of adding a view to an Entity Framework Code First Context. I wanted my solution to work with a view that does not yet exist. The view must be created (and removed if necessary) by a migration. I am fine with the fact that the view has the same name as the property in the context.

You can find an explanation about how to use pre-existing views in a context, as well as ideas on how to add a view via migrations. What was missing for me was a complete example that combines both aspects. So here is mine :-)

We start with a simple User class,

public class User
{
    public Guid Id { get; set; }
    public string Login { get; set; }
    public bool IsAdmin { get; set; }
}

and add this to a very simple context

public class ViewContext : DbContext
{

    public ViewContext()
        : base("ViewContext")
    {

    }
    
    public DbSet<User> Users { get; set; }

    protected override void OnModelCreating(DbModelBuilder modelBuilder)
    {
        modelBuilder.Entity<User>()
            .HasKey(u => u.Id)
            .Property(u => u.Id)
                .HasDatabaseGeneratedOption(DatabaseGeneratedOption.Identity);

        base.OnModelCreating(modelBuilder);
    }
}

Using the Package Manager Console we enable migrations and create an initial migration and apply it to the database:

enable-migrations
add-migration initial
update-database

Now I want to create two views, one that filters on admin users and the other that filters on normal users.

The first thing to to is, to create a new class that reflects the view to be created, adding a DbSet using that class to the context and create a migration for it.

public class AdminUser
{
    public Guid Id { get; set; }
    public string Login { get; set; }
}

public class ViewContext : DbContext
{
    public DbSet<AdminUser> AdminUsers { get; set; }
}

add-migration viewadminusers

The migration created after this change contains a CreateTable-Statement. This is not the desired thing to happen, so this has to be changed. It is possible to use the DbMigrations Sql-Method for this an pass in the SQL as a string. This is an easy solution, but it did not satisfy me. I wanted a solution that looks more like native migrations, like so:

public partial class viewadminusers : DbMigration
{
    public override void Up()
    {
        CreateView("dbo.AdminUsers", "SELECT * FROM Users WHERE IsAdmin = 1");
    }
    
    public override void Down()
    {
        DropView("dbo.AdminUsers");
    }
}

So I digged a little deeper, and found an approach to add new operations to DbMigrations. The example shows how to add check constraints, and it works all the same for views.

First of all we need a POCO class to describe our operation:

public class CreateViewOperation : MigrationOperation
{

    public CreateViewOperation(string name, string sql)
        : base(null)
    {
        this.Name = name;
        this.Sql = sql;
    }

    public string Name { get; set; }
    public string Sql { get; set; }

    public override bool IsDestructiveChange
    {
        get
        {
            return false;
        }
    }
}

The class must derive from MigrationOperation and holds properties for the name and the SQL-Body of the view. To be able to use this Operation in a Migration, we can use extension methods on DbMigration:

public static class CreateViewExtension
{
    public static void CreateView(this DbMigration migration, string name, string sql)
    {
        var createViewOperation = new CreateViewOperation(name, sql);
        ((IDbMigration)migration).AddOperation(createViewOperation);
    }

    public static void DropView(this DbMigration migration, string name)
    {
        var dropViewOperation = new DropViewOperation(name);
        ((IDbMigration)migration).AddOperation(dropViewOperation);
    }
}

With this code in place we can write the migration almost like stated in the above example, the only difference is that one must use the this-prefix in front of the method name which is close enough.

this.CreateView("dbo.AdminUsers", "SELECT * FROM Users WHERE IsAdmin = 1");

The thing that acutally translate the operation to SQL is a subclass of SqlServerMigrationSqlGenerator:

public class CustomSqlServerMigrationGenerator : SqlServerMigrationSqlGenerator
{
    protected override void Generate(MigrationOperation migrationOperation)
    {
        if (migrationOperation is CreateViewOperation)
        { 
            Generate(migrationOperation as CreateViewOperation);
            return;
        }

        base.Generate(migrationOperation);
    }

    protected virtual void Generate(CreateViewOperation createViewOperation)
    {
        using (var writer = Writer())
        {
            writer.WriteLine(
                "EXEC ('CREATE View {0} AS {1}')",
                Name(createViewOperation.Name),
                createViewOperation.Sql
            );
            Statement(writer);
        }
    }
}

The override of the general Generate(MigrationOperation) method checks if the operation is of type CreateViewOperation and hands over to the specialized Generate(CreateViewOperation) method. This method generates the SQL code using a special TextWriter and adds the resulting SQL using the Statement method.

Almost done! The only piece left is to add this SQL-Generator to the migrations configuration.

internal sealed class Configuration : DbMigrationsConfiguration<EFCodeFirstViews.ViewContext>
{
    public Configuration()
    {
        AutomaticMigrationsEnabled = false;
        SetSqlGenerator("System.Data.SqlClient", new CustomSqlServerMigrationGenerator());
    }
}

The SetSqlGenerator method of the DbMigrationsConfigurationType lets you add a SQL generator per provider type.

And this is it. The only thing that has to be done manually is to change the generated migrations file to use the CreateView method instead of create table.

Happy viewing!