I've been planning to write a blog post about how to achieve the so called "logical delete" or "soft delete" when using Entity Framework. This is the kind of requirement you have when you are not allowed to physically delete records from your database when deleting (e.g. for auditing or version tracking purposes), you only have to hide them from users.

This is not an unusual requirement and I myself have come up with a lot of ways of achieving this. The main problem is, of course, that EF has no explicit support for this, so you have to manually implement a solution. I have implemented a number of solutions over the years, but I have not been especially content with any of them so I figured I should finally come up with a way that I like.

This turned out to be quite challenging for a number of reasons. But I think I have a solution that I like :) During my "brainstorming", I came up with a lot of different ideas and looked at the solution from a number of different points of view. What follows is a series of blog posts, where I discuss the different solutions, their advantages and disadvantages. This introductory blog post aims at determining the criteria for an 'ideal" solution and defining the problems that come up when implementing soft delete.

The problems

When you implement soft delete, I have identified 4+3 main challenges, that you must be overcome:

• Challenge no. 1: The most obvious one is that you somehow have to flag records that are deleted.
• Challenge no. 2: Again quite obvious that somehow you have to "filter" the results of your queries so it only includes the not deleted records (I'll refer to them as active records).
• Challenge no. 3: The next one is not so obvious, but usually it is also a requirement. Since your using EF, you probably have navigation properties, so you have to make sure that if you include any related records, you only include the active ones.
• Challenge no. 4: Again not so obvious (and is not always a requirement): cascade deletes. How do you ensure that when a record is deleted (i.e. flagged), all the associated records are also deleted (i.e. flagged)?

These are the requirements that are the "business" requirements. The other three are more of a technical nature:

• Challenge no. 1: The most obvious challenge is that we need an automated solution to handle both flagging and loading. This way, we can add a new entity to the model and not have to worry about soft delete for that specific entity.
• Challenge no. 2: The solution has to be as efficient as possible, and not just on the code side, but also on the DB side (i.e. generating simple, optimizable queries).
• Challenge no. 3: A less obvious challenge and one that people not always recognize: avoiding abstraction leaking. What I mean is that ideally, one should have zero knowledge about this soft delete behavior. They should be able to use the usual EF methods to handle entities (e.g. .Remove() for deletion, .Include() the include related entities).

So there. These are the challenges that one should overcome when designing a solution like this. I have come up with a number of different solutions and as I said, a series of blog posts will follow that discuss my ideas. I'm not trying to say that I have come up with the ultimate, or the best solution — I never make such statements. I'm just a guy with a few ideas who thinks that EF is awesome (if you don't believe me, read this or this post).

Oh and one last thing: I'm cheating a little bit and I only take into consideration solutions that work on Microsoft's SQL Server. Again, if you read any of my posts, this shouldn't come as a surprise :)

See you all next time!.