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

Solution 1:

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

Solution 2:

Use EntityFramework.DynamicFilters. It allows you to create global filters that will be applied automatically (including against navigation properties) when queries are executed.

There is an example "IsDeleted" filter on the project page that looks like this:

modelBuilder.Filter("IsDeleted", (ISoftDelete d) => d.IsDeleted, false);

That filter will automatically inject a where clause on any query against an entity that is ISoftDelete. Filters are defined in your DbContext.OnModelCreating().

Disclaimer: I'm the author.

Solution 3:

One option would be to encapsulate the !IsDeleted into an extension method. Something like below is just an example. Beware its just to give you an idea of an extension method, the below won't compile.

public static class EnumerableExtensions
{
    public static T FirstOrDefaultExcludingDeletes<T>(this IEnumerable<T> source, Func<T, bool> predicate)
    {
        return source.Where(args => args != IsDeleted).FirstOrDefault(predicate);
    }
}

Usage:

_db.Users.FirstOrDefaultExcludingDeletes(UserId == id)