Deep Auditing - adamfoneil/Dapper.Repository GitHub Wiki

I use the term "deep auditing" to mean tracking all changes to rows over time to enable more powerful undo features and forensic analysis when necessary. There are native SQL Server features for this you should consider first: Change Data Capture and Temporal Tables. A limitation of CDC and Temporal Tables however is that they don't naturally capture the user who made a change. Your application must capture the user name and timestamp as part of your CRUD operations. I have a walkthrough on this in the Shallow Auditing topic. Deep Auditing is a bit different and captures a history of changes to all rows.

Using Dapper.Repository, you can implement a solution for tracking updates and deletions in your application as they happen. This works seamlessly within standard crud operations SaveAsync and DeleteAsync. You can apply this to any or all of the tables in your application.

My assumption here is you've implemented a data context and repository class in your application based on the readme instructions. I'll refer to this below as "your base repository" class. An example in this repo is here. A sample data context is here.

I don't have a fully open source example, but I'll share all necessary code snippets to give you enough context to implement your own approach. A complex feature like this with several moving pieces comes with some opinions as to how it should work. I felt this undercut the value of a standalone package. I'd rather share my approach than attempt to prescribe a singular, standard solution.

I created a walkthrough video that shows a real use of this here: OneDrive.

  1. Decide how you want to store change history. In my case I have a pair of tables in their own schema changes.Deletion and changes.Update. These store user and timestamp information along with json blobs containing change data.
Source 
[Table("Deletion", Schema = "changes")]
public class RowDelete
{
    public int Id { get; set; }

    [Required]
    [MaxLength(50)]
    public string UserName { get; set; }

    /// <summary>
    /// user local time
    /// </summary>
    public DateTime Timestamp { get; set; }

    [MaxLength(100)]
    [Required]
    public string TableName { get; set; }

    public int RowId { get; set; }

    /// <summary>
    /// json blob of deleted row
    /// </summary>
    [Required]
    public string RowData { get; set; }
}

[Table("Update", Schema = "changes")]
public class RowUpdate
{   
    public int Id { get; set; }

    [Required]
    [MaxLength(50)]
    public string UserName { get; set; }

    /// <summary>
    /// user local time
    /// </summary>
    public DateTime Timestamp { get; set; }
    
    [MaxLength(100)]
    [Required]
    public string TableName { get; set; }

    public int RowId { get; set; }

    /// <summary>
    /// json info about changed values (serialized from Changes property)
    /// </summary>
    [Required]
    public string ChangeData { get; set; }
}
  1. You need something to perform a low-level comparison of any two instances of a class. I use my own utility class ObjectComparer. It converts object properties to a dictionary of Diffs.
Source 
public static class ObjectComparer
{
    public const string Null = "<null>";

    public static Dictionary<string, Diff> Compare<T>(T original, T updated)
    {
        var originalDictionary = ToDictionary(original);
        var updatedDictionary = ToDictionary(updated);
        
        var diffs = originalDictionary.Join(updatedDictionary, (kp) => kp.Key, (kp) => kp.Key, (o, u) => new
        {
            ColumnName = o.Key,
            OldValue = o.Value,
            NewValue = u.Value
        }).Where(item => !item.OldValue.Equals(item.NewValue));

        Dictionary<string, Diff> results = new();

        foreach (var item in diffs) results.Add(item.ColumnName, new Diff(item.OldValue, item.NewValue));

        return results;
    }

    public static async Task DeferenceKeysAsync(
        this Dictionary<string, Diff> diffs,
        IDbConnection connection,
        Dictionary<string, Func<IDbConnection, object, Task<string>>> lookups)
    {
        foreach (var kp in lookups.Where(item => diffs.ContainsKey(item.Key)))
        {
            var oldValueText = (!diffs[kp.Key].OldValue.ToString().Equals(Null)) ? await kp.Value.Invoke(connection, diffs[kp.Key].OldValue) : Null;
            var newValueText = (!diffs[kp.Key].NewValue.ToString().Equals(Null)) ? await kp.Value.Invoke(connection, diffs[kp.Key].NewValue) : Null;
            diffs[kp.Key] = new Diff(oldValueText, newValueText);
        }
    }

    public static Dictionary<string, object> ToDictionary(object source)
    {
        var properties = source
            .GetType()
            .GetProperties()
            .Where(pi => pi.CanWrite && IsMapped(pi)).ToArray();

        var result = new Dictionary<string, object>();

        foreach (var property in properties) result.Add(property.Name, property.GetValue(source) ?? DBNull.Value);

        return result;

        bool IsMapped(PropertyInfo propertyInfo)
        {
            var attr = propertyInfo.GetCustomAttribute<NotMappedAttribute>();
            return (attr == null);
        }
    }        
}

public record Diff
{
    public Diff(object oldValue, object newValue)
    {
        OldValue = (oldValue == DBNull.Value) ? ObjectComparer.Null : oldValue;
        NewValue = (newValue == DBNull.Value) ? ObjectComparer.Null : newValue;
    }

    public object OldValue { get; init; }
    public object NewValue { get; init; }
}
  1. Create a repository class that inherits from your base repository. In my case, I have ChangeTrackingRepository<TModel>
Source 
public class ChangeTrackingRepository<TModel> : BaseRepository<TModel> where TModel : IModel<int>
{
    private TModel _current;
    private string _jsonDeleted;

    public ChangeTrackingRepository(DataContext context) : base(context)
    {
    }        

    protected virtual IEnumerable<string> IgnoreProperties => new[]
    {
        nameof(BaseTable.ModifiedBy),
        nameof(BaseTable.DateModified),
        nameof(BaseTable.DateCreated),
        nameof(BaseTable.CreatedBy)
    };

    /// <summary>
    /// key = column name, value = query that derefs an FK value into a readable text value
    /// </summary>
    protected virtual Dictionary<string, Func<IDbConnection, object, Task<string>>> KeyLookups => default;

    protected override async Task BeforeSaveAsync(IDbConnection connection, SaveAction action, TModel model, IDbTransaction txn = null)
    {
        if (action == SaveAction.Update && model.Id != default)
        {
            _current = await GetAsync(model.Id);
        }

        await base.BeforeSaveAsync(connection, action, model, txn);
    }

    protected override async Task AfterSaveAsync(IDbConnection connection, SaveAction action, TModel model, IDbTransaction txn = null)
    {
        if (action == SaveAction.Update)
        {
            var changes = ObjectComparer.Compare(_current, model);

            foreach (var ignore in IgnoreProperties)
            {
                if (changes.ContainsKey(ignore)) changes.Remove(ignore);
            }                

            if (changes.Any())
            {
                if (KeyLookups != default)
                {
                    await changes.DeferenceKeysAsync(connection, KeyLookups);
                }

                var log = new RowUpdate()
                {
                    UserName = Context.User.UserName,
                    Timestamp = Context.User.LocalTime,
                    TableName = typeof(TModel).Name,
                    RowId = model.Id,
                    ChangeData = JsonSerializer.Serialize(changes)
                };

                await connection.InsertAsync<RowUpdate, int>(log);
            }
        }

        await base.AfterSaveAsync(connection, action, model, txn);
    }

    protected override async Task BeforeDeleteAsync(IDbConnection connection, TModel model, IDbTransaction txn = null)
    {
        await base.BeforeDeleteAsync(connection, model, txn);

        _jsonDeleted = JsonSerializer.Serialize(model, new JsonSerializerOptions()
        {
            WriteIndented = true
        });
    }

    protected override async Task AfterDeleteAsync(IDbConnection connection, TModel model, IDbTransaction txn = null)
    {
        await base.AfterDeleteAsync(connection, model, txn);

        if (!string.IsNullOrEmpty(_jsonDeleted))
        {
            await connection.InsertAsync<RowDelete, int>(new RowDelete()
            {
                UserName = Context.User.UserName,
                Timestamp = Context.User.LocalTime,
                TableName = typeof(TModel).Name,
                RowId = model.Id,
                RowData = _jsonDeleted
            });

            _jsonDeleted = null;
        }
    }

    /// <summary>
    /// was a certain column modified from its last saved value?
    /// </summary>
    protected bool IsModified(TModel model, Func<TModel, object> accessor) => !(accessor.Invoke(model).Equals(accessor.Invoke(_current)));    
}
  1. In your data context class, decide which tables need change tracking, and set them up accordingly. In the next snippet, a lot of code is omitted for clarity, but the main thing to see is that several tables are setup as repository properties on the DataContext object that all use ChangeTrackingRepository<TModel>.
Source 
public partial class DataContext : SqlServerContext<UserInfoResult>
{
    private readonly AuthenticationStateProvider _authState;
    private readonly IDistributedCache _cache;
    private readonly UserInfoResult _defaultUser;

    // code omitted for clarity

    // add repository properties for your tables that require change tracking
    public ChangeTrackingRepository<ItemReminder> ItemReminders => new ChangeTrackingRepository<ItemReminder>(this);
    public ChangeTrackingRepository<ItemTriggerWeight> ItemWeightTriggers => new ChangeTrackingRepository<ItemTriggerWeight>(this);
    public ChangeTrackingRepository<ItemTriggerSexSpecies> ItemSexSpeciesTriggers => new ChangeTrackingRepository<ItemTriggerSexSpecies>(this);
    public ChangeTrackingRepository<ScreeningQuestion> ScreeningQuestions => new ChangeTrackingRepository<ScreeningQuestion>(this);
}
  1. At this point, it's a matter of using your data context and repository properties in your application. Any save and delete operations against your repositories based on ChangeTrackingRepository<TModel> will capture changes in your tables that you created in step 1.
⚠️ **GitHub.com Fallback** ⚠️