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

Question

I am using Entity Framework Code First. I override SaveChanges in DbContext to allow me to do a "soft delete":

if (item.State == EntityState.Deleted && typeof(ISoftDelete).IsAssignableFrom(type))
{
    item.State = EntityState.Modified;
    item.Entity.GetType().GetMethod("Delete")
        .Invoke(item.Entity, null);

    continue;
}

Which is great, so the object knows how to mark itself as a soft delete (In this case it just sets IsDeleted to true).

My question is how can I make it such that when I retrieve the object it ignores any with IsDeleted? So if I said _db.Users.FirstOrDefault(UserId == id) if that user had IsDeleted == true it would ignore it. Essentially I want to filter?

Note: I do not want to just put && IsDeleted == true That's why I am marking the classes with an interface so the remove knows how to "Just Work" and I'd like to somehow modify the retrieval to know how to "Just Work" also based on that interface being present.

Answer 1

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.

Answer 2

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

Answer 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)

Answer 4

You can use Global Query Filters on Entity Framework Core 2.0.

protected override void OnModelCreating(ModelBuilder modelBuilder)
{
    modelBuilder.Entity<Blog>().Property<string>("TenantId").HasField("_tenantId");

    // Configure entity filters
    modelBuilder.Entity<Blog>().HasQueryFilter(b => EF.Property<string>(b, "TenantId") == _tenantId);
    modelBuilder.Entity<Post>().HasQueryFilter(p => !p.IsDeleted);
}

Answer 5

Great question.

You would need to intercept SQL query before it gets executed somehow, then add additional where clause to remove 'deleted' items from selection. Unfortunately, Entity doesn't have GetCommand that can be used to change the query.

Perhaps EF Provider Wrapper which sits in the right place could be modified to allow for query change.

Or, u can utilize QueryInterceptor but each query would have to use InterceptWith(visitor) to change the expressions...

So, I would concentrate on this approach as there is AFAIK no other option then intercepting the query and fixing it (if you want to keep code that queries unchanged).

Anyway, if you figure out something useful, let us know.