E.F.6, DB first, soft delete - condition mapping of computed column

c# edmx ef-database-first entity-framework-6 soft-delete


I'd like to implement soft delete feature on application that is using E.F.6 with database first (.edmx) approach. Following tips and tricks from links: link1 and link2 I managed to do the following:

  • Added column IsDeleted on table
  • Added calculated column IsDeletedMapping on table, that has same value as IsDeleted
  • Added ISoftDelete interface, which will be implemented on table entities

I then added code to handle Delete action on entities in my SqlRepository Delete method.

// if has ISoftDelete interface perform soft delete.
if (typeof (ISoftDelete).IsAssignableFrom(typeof (T)))
     entry.State = EntityState.Modified;
     var tempEntry = entry.Entity as ISoftDelete;
     tempEntry.IsDeleted = true;
// else, mark entity state as Deleted
     entry.State = EntityState.Deleted;

This means entities that implement ISoftDelete interface will instead of be deleted in DB, just be updated with IsDeleted = true. Also the IsDeletedMapping will be set to true, since it's calculated from IsDeleted.

The problem that I now run into is how to filter the IsDeleted = false in EF queries.

What I tried is mapping condition on IsDeletedMapping, since this means EF will automatically filter this for us.

Condition mapping of IsDeletedMapping

But here lies the problem. IsDeletedMapping has StoreGeneratedPattern property set to Computed and so I get an error:

Error 2016: Condition cannot be specified for Column member 'IsDeletedMapping' because it is marked with a 'Computed' or 'Identity' StoreGeneratedPattern.

So the question is: Is there any workaround to set condition mapping on computed column? Also if you have any better approach on how to implement soft delete I'll be more then happy to try it. Keep in mind that implementation must automatically filter soft deleted records.

Thank your for your answers!

5/23/2017 12:23:56 PM

Popular Answer

I think your implementation is too complicated. Here's what we did:

  1. Add IsDeleted (bool, not null) column to the tables
  2. Map the Delete function of the entity to a stored procedure which actually sets IsDeleted = 0, instead of a real delete. This will force you to map the Insert and Update functions too though, but we preferred procedures anyway.
  3. Filter the entity by IsDeleted = 0 in the edmx

Now, all your entities are automatically filtered by IsDeleted = 0, and your Delete method in the repository can do a real delete, hiding the fact that records are not really deleted from the database. So if you ever want to do real deletes, or maybe do soft-deletes only on some of the entities, your code won't be aware of it.

Don't forget that once a table field is an entity filter, it can't be mapped to an entity property. Your code won't have an IsDeleted property in the entity class.

5/3/2016 10:10:40 AM

Related Questions


Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow