Implementing “Soft Deletes”

Question: “For some of our Entities, we want the corresponding database records not to be physically deleted when a User deletes the Entity and saves changes. Instead, the corresponding record should just be flagged as deleted in the database.

“To accomplish this, we have a Status column that contains an enum value stating whether the row is deleted or not. When querying tables that include such columns, the Status always needs to be specified as a parameter to filter out the deleted records.

“I did not find an obvious way to implement this delete behaviour in our Entity classes.  Do you have any samples?

“Also, how can we extend or override DevForce’s GetEntity() methods so they filter out the deleted entities by default, without any additional clause?”

Answer: This is a pattern we often see. There are a number of ways to go about this:

1.                    Add a "Fetching" handler to the PersistenceManager that examines the query and adds this clause if necessary. The Fetching event is raised just before the PersistenceManager sends a query to the server; the query is included in the event args for the Fetching handler, and you can modify it before DevForce applies it.

2.                    Add a "Fetched" handler to the PersistenceManager that removes "inactive" entities from the cache if they happen to be picked up (e.g., through a span).  This is essential if you add spans that could retrieve an "inactive/deleted" record. You will want to use the overload of PersistenceManager.RemoveEntities() method that does not disturb the query cache.

3.                    Create a custom subclass of RdbQuery that always adds this clause.  Then always use your class instead of the raw RdbQuery.

4.                    Write your own PersistenceManager adapter that covers GetEntities, etc.

The "Fetching" and "Fetched" event handlers are strong candidates because they work even within Relation Properties. Consider, for example, Order.OrderDetails. If the OrderDetail object has an " ActiveFlag  column, you have to intercept the hidden query either by the use of a "Fetching" handler, or by overriding the "OrderDetails" property itself.

Let's assume that all business objects that have an "ActiveFlag" property descended from an abstract entity class (e.g., "ActiveEntity").

You might initialize your PersistenceManager as follows:

There is another approach that might tempt you, but which should be avoided. There is an "SQL Where Clause" mapping option which lets you put a filtering constraint in the object definition itself. This is great if the value of the filter column is invariant. It is not good if the value of the filter column can change -- as is the case with the "inactive" or "deleted" column.  Therefore, do not consider this option.