c# - 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.