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.
See Question&Answers more detail:
os