AutoCrud: how to combine softdelete and hard delete together in a single service?

ServiceStack Auto Query
1

I use SQL Server 2019 with System Versioning, and I have added AutoCrud, with AutoPopulate of audit fields. It is all working brilliantly for the CRUD actions.

However, for compliance reasons, deleting a record requires a two step process:

  1. Call the soft-delete service, which simply updates the record’s audit event column to “Deleted”. This is required for compliance.
  2. Call the hard-delete service, which deletes the record from the table. The history table is then automatically updated with the record data from step (1) (i.e. showing a “Deleted” event).

To improve the delete process, I would like to create a custom implementation that combines (1) and (2) into a single service call?

If this is possible, is there a code sample?

2

I managed to solve my problem with the following custom implementation of the Delete:

public class ConfigurationService : Service
{
public IAutoQueryDb? AutoQuery { get; set; } = null;

// Performs a soft delete and then a hard delete
public DeleteConfigurationResponse Delete(DeleteConfiguration request)
{
using var db = AutoQuery!.GetDb(base.Request);

// step 1: soft delete
var response1 = (SoftDeleteConfigurationResponse)AutoQuery.Patch<Configuration>(
  new SoftDeleteConfiguration { Id = request.Id }, base.Request);

// step 2: hard delete
var response2 = (DeleteConfigurationResponse)AutoQuery.Delete<Configuration>(
  new DeleteConfiguration { Id = request.Id }, base.Request);

return response2;

}

This works perfectly, with the audit fields still being autopopulated. But, I would be grateful to know if there is a better way?

All help appreciated.

3

If you want the Audit History of both operations then you’d need 2 separate Service operations as you’re doing here.

I’ve added a new PartialUpdate API to IAutoQueryDb which may be able to save you defining the SoftDeleteConfiguration operation by re-using the DeleteConfiguration to perform the Soft Delete, so if you populate audit fields on your DeleteConfiguration API, e.g:

[AutoPopulate(nameof(IAudit.DeletedDate), Eval = "utcNow")]
[AutoPopulate(nameof(IAudit.DeletedBy),   Eval = "userAuthName")]
public class DeleteConfiguration : IDeleteDb<Configuration> 
{
    public int Id { get; set; }
}

You should be able to use the same DTO to perform the Soft Delete where it will populate the DeletedDate & DeletedBy audit fields, whilst the Delete API continues to perform the hard delete, e.g:

public DeleteConfigurationResponse Delete(DeleteConfiguration request)
{
    // step 1: soft delete
    var response1 = AutoQuery.PartialUpdate<Configuration>(
      request, base.Request);

    // step 2: hard delete
    var response2 = AutoQuery.Delete(request, base.Request);

    return response2;
}

Although I’m not sure why in your example you’re creating a new instance instead of using the DeleteConfiguration Request DTO the service was called with.

This new API is available in the latest v5.9.3+ that’s now available on MyGet.

New AutoApply behavior

As it’s related I should mention the latest ServiceStack now includes a AuditBase.cs class with Created*, Modified* & Deleted* Date/By properties whose properties are populated with the new [AutoApply(Behavior)] attribute where you can apply generic behavior to your AutoQuery operations, in this case the in-built behavios populates the AuditBase properties where if you had DeletedBy and DeletedDate fields you can instead populate them with.

[AutoApply(Behavior.AuditDelete)]
public class DeleteConfiguration : IDeleteDb<Configuration> 
{
    public int Id { get; set; }
}

But you can also add your custom behavior by extending the metadata on each AutoQuery Request DTO with additional attributes. So instead of adding multiple [AutoPopulate] attributes on each DTO you can define a custom behavior like:

[AutoApply("MyDelete")]
public class DeleteConfiguration : IDeleteDb<Configuration> 
{
    public int Id { get; set; }
}

Then register its behavior in AutoCrudMetadataFilters using it as a marker to add AutoQuery attributes that auto populates your preferred fields, e.g. Assuming you’re using a MyBase class with MyDeletedDate and MyDeletedBy properties:

void MyAuditFilter(AutoCrudMetadata meta)
{
  if (meta.HasAutoApply("MyDelete"))
  {
    meta.Add(new AutoPopulateAttribute(nameof(MyBase.MyDeletedDate)) {
        Eval = "utcNow"
    });
    meta.Add(new AutoPopulateAttribute(nameof(MyBase.MyDeletedBy)) {
        Eval = "userAuthName"
    });
  }
}

Plugins.Add(new AutoQueryFeature {
    AutoCrudMetadataFilters = { MyAuditFilter },
});

I’m currently writing the release notes which will include more info on how this works but you can find an example project & demo that uses this at GitHub - NetCoreApps/BookingsCrud: Code-first, declarative, multi-user .NET Core Booking system using Auto CRUD & ServiceStack Studio project.

4

The partial update API sounds very interesting and I will certainly see if I can use it. My approach requires defining an implementation for each delete service (i.e. every relevant table).

At the moment,my event audit is recorded using SQL Server’s System Versioning and not ServiceStack’s Audit history.

I also do not have the same audit fields that are in your AuditBase.cs:

public enum DataEventType : byte
{
Created = 1,
Updated = 2,
Deleted = 3
}

public interface IAudit
{
    byte DataEventId { get; set; }     // 1 Created, 2 Updated or 3 Deleted from DataEventType 
    DateTime DataEventOn { get; set; }
    Guid DataEventBy { get; set; }
}

// Use as base class for Types persisted in audited database tables
public abstract class AuditBase : IAudit
{
    [Required] public byte DataEventId { get; set; }
    [Required] public DateTime DataEventOn { get; set; }
    [Required] public Guid DataEventBy { get; set; }
}

Thank you.

PS: Well caught on my mistake creating a new instance for the DeleteConfiguration Request DTO.

5

If you’re not using ServiceStack’s Audit History then you should be able to make the update directly with the DB, something like:

public DeleteConfigurationResponse Delete(DeleteConfiguration request)
{
    using var db = AutoQuery!.GetDb(base.Request);
    db.UpdateOnly<Configuration>(() => new Configuration {
            DataEventId = (byte) DataEventType.Deleted,
            DataEventOn = DateTime.UtcNow,
        }, where: x => x.Id == request.Id)
    //...
}

I’ve also added OnBefore/After events for Create/Update/Patch/Delete operations (Sync + Async), so you could achieve the same thing using the OnBeforeCreate event hook, e.g:

Plugins.Add(new AutoQueryFeature {
    AutoCrudMetadataFilters = { MyAuditFilter },
    OnBeforeDelete = meta => {
        if (meta.Dto is DeleteConfiguration dto) {
            db.UpdateOnly(() => new Configuration {
                    DataEventId = (byte) DataEventType.Deleted,
                    DataEventOn = DateTime.UtcNow,
                }, where: x => x.Id == dto.Id);
        }
    },
});