Purging Deleted Data

Hello,

I'm looking to purge deleted data a certain time period after they were "deleted".  We have a multitenant system and administrators of the tenant can specify data retention policies.   I use soft deletion purely to provide "undo" feature.  This will also allow users to perform a "hard delete" of the data.  Most of our end users seems to be concerned (perhaps obsessed is more appropiate) with where their data is and how long its stored there. They want as much control as they can get.  On my side, I don't want to penalize the user for making a mistake, hence the soft delete.

Scanning the forums, I noticed this request has been around for a while.  Has any progress been made regarding this feature?  I'm essentially just looking for two methods, namely:

// purge a specific entity. When called on a non deleted entity, it will actually permanently delete the entity.

  UnitOfWork.Purge(Entity entity);

// purge all entities of type that has been deleted "ts" ago.

  UnitOfWork.Purge<TEntity>(TimeSpan ts);

There are cases where the following may result in better performance (say when I want to purge orphan entities in a many to many relationship).

// allows me to purge data base on another criteria, like when it contains no relationships

UnitOfWork.Purge<TEntity>(this IQueryable<TEntity> queryable);

Much appreciated,

Werner

No, sorry, this is still not planned in.  Our usual assumption is that handling of soft deleted data is a policy issue and will be taken care of through a scheduled job or by a DBA.  To purge soft deleted entities, you would currently have to issue DELETE commands directly.  (You could of course use LightSpeedContext.DataProviderObjectFactory and IUnitOfWork.PrepareCommand to execute these commands.)

The tricky bit might be to get the table names and DeletedOn column name in a portable way (i.e. so as not to hardwire mappings into your code).  We could probably extend the Metadata assembly to provide this info.  With this and the methods mentioned above it should not be too hard to write your desired API as a set of extension methods on IUnitOfWork.  Let us know!

Hi Ivan,

I was hoping that LightSpeed can take care of this for me.  I don't have a DBA on this project and have to pretty much take care of everything from UI to database, deployment and support.  This rather common for startup-like projects.  I'll go with whatever you propose as the solution, and would appreciate any assistance to complete this task.

Thanks,

Werner

Hi Werner,

Pardon the slight delay in replying.  We decided that now would be a good time to implement this feature but the discussion about APIs became spirited and the Cyber Police had to intervene to separate us.

The next nightly build will contain a new overload of Remove: Remove(Query query, bool forceHardDelete).  If you pass true for forceHardDelete, then LightSpeed will ignore any soft delete markers, and perform a hard delete instead.

Note that the Remove is applied to the results of the query, and the query is evaluated in the normal way.  So if you specify live entities in the query, then we will hard delete those live entities.  Moreover, since queries normally don't return soft-deleted items, you'll need to specify IncludeDeleted = true on the Query object to tell it to apply to soft-deleted items as well as live ones.

For example, to purge all soft-deleted Widgets which were deleted more than 30 days ago:

unitOfWork.Remove(
  new Query(
    typeof(Widget),
    Entity.Attribute("DeletedOn") < DateTime.Now.AddDays(-30)
  ) { IncludeDeleted = true },
  true);

Or to purge *all* soft-deleted Widgets:

unitOfWork.Remove(
  new Query(
    typeof(Widget),
    Entity.Attribute("DeletedOn") != null
  ) { IncludeDeleted = true },
  true);

Remember, if you forget the DeletedOn filter then LightSpeed will include live entities in the hard delete!

Obviously, you can, and probably should, create extension methods on UnitOfWork to encapsulate common purge patterns.

A couple of notes:

1. For compatibility reasons, Remove(Query, bool) is defined on UnitOfWork, not IUnitOfWork.  If you are using a LINQ unit of work then this will not affect you, but if you are using the raw IUnitOfWork interface then you will need to cast it to UnitOfWork to access this method.

2. By default, the DeletedOn field contains the local time of the machine where LightSpeed is running.  If you have to deal with machines whose clocks may be incorrect, or which are spread across multiple time zones, you will need to bear this in mind.

This feature will be in the next nightly build and should currently be treated as beta.  Please let us know if you run into any bugs.

Thank you. 

Werner