Entity Framework Snapshot History

Question

I am trying to figure out how to use Code First Entity Framework and keep a snapshot history of certain tables. This means that for each table I want to track, I would like to have a duplicate table postfixed _History. Every time I make a change to a tracked table row, the data from the database is copied to the history table before the new data is saved to the original table, with a Version column that gets incremented.

So imagine I have a table called Record. I have a row (ID:1,Name:One,Version:1). When I change this to (ID1:Name:Changed,Version:2), the Record_History table gets a row (ID:1,Name:One,Version:1).

I have seen good examples and know there are libraries around to keep an audit log of changes using Entity Framework but I need a complete snapshot of the entity at each revision for SQL reporting.

In my C# I have a base class that all my "Tracked" tables equivalent entity classes inherit from:

public abstract class TrackedEntity
{
    [Column(TypeName = "varchar")]
    [MaxLength(48)]
    [Required]
    public string ModifiedBy { get; set; }

    [Required]
    public DateTime Modified { get; set; }

    public int Version { get; set; }
}

An example of one of my entity classes is:

public sealed class Record : TrackedEntity
{
    [Key]
    public int RecordID { get; set; }

    [MaxLength(64)]
    public string Name { get; set; }
}

Now for the part I am stuck with. I would like to avoid typing out and maintaining a separate _History class for every entity I make. I would like to do something intelligent to tell my DbContext class that every DbSet it owns with a type inheriting from TrackedEntity should have a history counterpart table, and whenever an entity of that type is saved, to copy the original values from the database to the history table.

So in my DbContext Class I have a DbSet for my records (and more DbSets for my other entities)

public DbSet<Record> Records { get; set; }

I have overridden the OnModelCreating method so I can inject the mapping for the new _History tables. However I cannot figure out how to use reflection to pass the Type of each entity into the DbModelBuilder.

    protected override void OnModelCreating(DbModelBuilder modelBuilder)
    {
        //map a history table for each tracked Entity type
        PropertyInfo[] properties = GetType().GetProperties();
        foreach (PropertyInfo property in properties.Where(p => p.PropertyType.IsGenericType 
            && p.PropertyType.Name.StartsWith("DbSet") 
            && p.PropertyType.GetGenericArguments().Length > 0
            && p.PropertyType.GetGenericArguments()[0].IsSubclassOf(typeof(TrackedEntity))))
        {
            Type type = property.PropertyType.GetGenericArguments()[0];
            modelBuilder.Entity<type>().Map(m => //code breaks here, I cannot use the type variable it expects a hard coded Type
            {
                m.ToTable(type.Name + "_History");
                m.MapInheritedProperties();
            });
        }
    }

I'm not even sure if using the modelBuilder like this will even generate the new History tables. I also don't know how to handle saving, I'm not sure if the entity mapping means the changes are then saved on both tables? I can create a SaveChanges method in my DbContext that can loop through my entities but I don't know how to make an entity save to a second table.

    public int SaveChanges(string username)
    {
        //duplicate tracked entity values from database to history tables
        PropertyInfo[] properties = GetType().GetProperties();
        foreach (PropertyInfo property in properties.Where(p => p.PropertyType.IsGenericType
            && p.PropertyType.Name.StartsWith("DbSet")
            && p.PropertyType.GetGenericArguments().Length > 0
            && p.PropertyType.GetGenericArguments()[0].IsSubclassOf(typeof(TrackedEntity))))
        {
            foreach (TrackedEntity entity in (DbSet<TrackedEntity>)property.GetValue(this, null))
            {
                entity.Modified = DateTime.UtcNow;
                entity.ModifiedBy = username;
                entity.Version += 1;

                //Todo: duplicate entity values from database to history tables
            }
        }
        return base.SaveChanges();
    }

Sorry for such a long question, it's quite a complicated issue. Any help would be appreciated.

Answer 1

For anyone else wanting to track history in the same way, here is the solution I settled with. I didn't manage to find a way to avoid creating separate history classes for each tracked class.

I Created a base class from which my entities can inherit:

public abstract class TrackedEntity
{
    [Column(TypeName = "varchar")]
    [MaxLength(48)]
    [Required]
    public string ModifiedBy { get; set; }

    [Required]
    public DateTime Modified { get; set; }

    public int Version { get; set; }
}

For each entity I create a normal entity class but inherit from my base:

public sealed class Record : TrackedEntity
{
    [Key]
    public int RecordID { get; set; }

    [MaxLength(64)]
    public string Name { get; set; }

    public int RecordTypeID { get; set; }

    [ForeignKey("RecordTypeID")]
    public virtual RecordType { get; set; }
}

For each entity I also create a history class (Always an exact copy but with the Key column moved, and with all foreign keys removed)

public sealed class Record_History : TrackedEntity
{
    [Key]
    public int ID { get; set; }

    public int RecordID { get; set; }

    [MaxLength(64)]
    public string Name { get; set; }

    public int RecordTypeID { get; set; }
}

Finally I created an overload of the SaveChanges method in my context class, this updates the history as needed.

public class MyContext : DbContext
{
    ..........

    public int SaveChanges(string username)
    {
        //Set TrackedEntity update columns
        foreach (var entry in ChangeTracker.Entries<TrackedEntity>())
        {
            if (entry.State != EntityState.Unchanged && !entry.Entity.GetType().Name.Contains("_History")) //ignore unchanged entities and history tables
            {
                entry.Entity.Modified = DateTime.UtcNow;
                entry.Entity.ModifiedBy = username;
                entry.Entity.Version += 1;

                //add original values to history table (skip if this entity is not yet created)                 
                if (entry.State != EntityState.Added && entry.Entity.GetType().BaseType != null)
                {
                    //check the base type exists (actually the derived type e.g. Record)
                    Type entityBaseType = entry.Entity.GetType().BaseType;
                    if (entityBaseType == null)
                        continue;

                    //check there is a history type for this entity type
                    Type entityHistoryType = Type.GetType("MyEntityNamespace.Entities." + entityBaseType.Name + "_History");
                    if (entityHistoryType == null)
                        continue;

                    //create history object from the original values
                    var history = Activator.CreateInstance(entityHistoryType);
                    foreach (PropertyInfo property in entityHistoryType.GetProperties().Where(p => p.CanWrite && entry.OriginalValues.PropertyNames.Contains(p.Name)))
                        property.SetValue(history, entry.OriginalValues[property.Name], null);

                    //add the history object to the appropriate DbSet
                    MethodInfo method = typeof(MyContext).GetMethod("AddToDbSet");
                    MethodInfo generic = method.MakeGenericMethod(entityHistoryType);
                    generic.Invoke(this, new [] { history });
                }
            }
        }

        return base.SaveChanges();
    }

    public void AddToDbSet<T>(T value) where T : class
    {
        PropertyInfo property = GetType().GetProperties().FirstOrDefault(p => p.PropertyType.IsGenericType
            && p.PropertyType.Name.StartsWith("DbSet")
            && p.PropertyType.GetGenericArguments().Length > 0
            && p.PropertyType.GetGenericArguments()[0] == typeof(T));
        if (property == null)
            return;

        ((DbSet<T>)property.GetValue(this, null)).Add(value);
    }
    ..........
}

Then whenever I save changes I use the new method, and pass in the current username. I wish I could avoid using the _History classes as they need to be maintained alongside the main entity class, and are easy to forget.

Answer 2

The simple way to do is SQLserver CDC. More here http://technet.microsoft.com/en-us/library/bb522489(v=sql.105).aspx

Answer 3

Have a look at Temporal tables (system-versioned temporal tables) if you are using SQL Server 2016< or Azure SQL.

https://learn.microsoft.com/en-us/sql/relational-databases/tables/temporal-tables?view=sql-server-ver15

From documentation:

Database feature that brings built-in support for providing information about data stored in the table at any point in time rather than only the data that is correct at the current moment in time. Temporal is a database feature that was introduced in ANSI SQL 2011.

I wrote a complete guide how to implement it with Entity Framework Core without any third party libraries here. Should be possible to use Entity Framework as well but not tested.

https://stackoverflow.com/a/64244548/3850405