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