How can I automatically filter out soft deleted entities with Entity Framework?

I’ve got soft delete working for all my entities and soft deleted items are not retrieved via the context using a technique suggested by this answer. That includes when you access the entity via navigation properties.

Add an IsDeleted discriminator to every entity that can be soft deleted. Unfortunately I haven’t worked out how to do this bit based on the entity deriving from an abstract class or an interface (EF mapping doesn’t currently support interfaces as an entity):

protected override void OnModelCreating(DbModelBuilder modelBuilder)
{
   modelBuilder.Entity<Foo>().Map(m => m.Requires("IsDeleted").HasValue(false));
   modelBuilder.Entity<Bar>().Map(m => m.Requires("IsDeleted").HasValue(false));

   //It's more complicated if you have derived entities. 
   //Here 'Block' derives from 'Property'
   modelBuilder.Entity<Property>()
            .Map<Property>(m =>
            {
                m.Requires("Discriminator").HasValue("Property");
                m.Requires("IsDeleted").HasValue(false);
            })
            .Map<Block>(m =>
            {
                m.Requires("Discriminator").HasValue("Block");
                m.Requires("IsDeleted").HasValue(false);
            });
}

Override SaveChanges and find all the entries to be deleted:

Edit
Another way to override the delete sql is to change the stored procedures generated by EF6

public override int SaveChanges()
{
   foreach (var entry in ChangeTracker.Entries()
             .Where(p => p.State == EntityState.Deleted 
             && p.Entity is ModelBase))//I do have a base class for entities with a single 
                                       //"ID" property - all my entities derive from this, 
                                       //but you could use ISoftDelete here
    SoftDelete(entry);

    return base.SaveChanges();
}

The SoftDelete method runs sql directly on the database because discriminator columns cannot be included in entities:

private void SoftDelete(DbEntityEntry entry)
{
    var e = entry.Entity as ModelBase;
    string tableName = GetTableName(e.GetType());
    Database.ExecuteSqlCommand(
             String.Format("UPDATE {0} SET IsDeleted = 1 WHERE ID = @id", tableName)
             , new SqlParameter("id", e.ID));

    //Marking it Unchanged prevents the hard delete
    //entry.State = EntityState.Unchanged;
    //So does setting it to Detached:
    //And that is what EF does when it deletes an item
    //http://msdn.microsoft.com/en-us/data/jj592676.aspx
    entry.State = EntityState.Detached;
}

GetTableName returns the table to be updated for an entity. It handles the case where the table is linked to the BaseType rather than a derived type. I suspect I should be checking the whole inheritance hierarchy….
But there are plans to improve the Metadata API and if I have to will look into EF Code First Mapping Between Types & Tables

private readonly static Dictionary<Type, EntitySetBase> _mappingCache 
       = new Dictionary<Type, EntitySetBase>();

private ObjectContext _ObjectContext
{
    get { return (this as IObjectContextAdapter).ObjectContext; }
}

private EntitySetBase GetEntitySet(Type type)
{
    type = GetObjectType(type);

    if (_mappingCache.ContainsKey(type))
        return _mappingCache[type];

    string baseTypeName = type.BaseType.Name;
    string typeName = type.Name;

    ObjectContext octx = _ObjectContext;
    var es = octx.MetadataWorkspace
                    .GetItemCollection(DataSpace.SSpace)
                    .GetItems<EntityContainer>()
                    .SelectMany(c => c.BaseEntitySets
                                    .Where(e => e.Name == typeName 
                                    || e.Name == baseTypeName))
                    .FirstOrDefault();

    if (es == null)
        throw new ArgumentException("Entity type not found in GetEntitySet", typeName);

    _mappingCache.Add(type, es);

    return es;
}

internal String GetTableName(Type type)
{
    EntitySetBase es = GetEntitySet(type);

    //if you are using EF6
    return String.Format("[{0}].[{1}]", es.Schema, es.Table);

    //if you have a version prior to EF6
    //return string.Format( "[{0}].[{1}]", 
    //        es.MetadataProperties["Schema"].Value, 
    //        es.MetadataProperties["Table"].Value );
}

I had previously created indexes on natural keys in a migration with code that looked like this:

public override void Up()
{
    CreateIndex("dbo.Organisations", "Name", unique: true, name: "IX_NaturalKey");
}

But that means that you can’t create a new Organisation with the same name as a deleted Organisation. In order to allow this I changed the code to create the indexes to this:

public override void Up()
{
    Sql(String.Format("CREATE UNIQUE INDEX {0} ON dbo.Organisations(Name) WHERE IsDeleted = 0", "IX_NaturalKey"));
}

And that excludes deleted items from the index

Note
While navigation properties are not populated if the related item is soft deleted, the foreign key is.
For example:

if(foo.BarID != null)  //trying to avoid a database call
   string name = foo.Bar.Name; //will fail because BarID is not null but Bar is

//but this works
if(foo.Bar != null) //a database call because there is a foreign key
   string name = foo.Bar.Name;

P.S. Vote for global filtering here https://entityframework.codeplex.com/workitem/945?FocusElement=CommentTextBox# and filtered includes here

More Related Contents:

Leave a Comment Cancel reply