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

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