title: Entity Framework Snapshot History
body: <p>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.</p>
  
  <p>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).</p>
  
  <p>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.</p>
  
  <p>In my C# I have a base class that all my "Tracked" tables equivalent entity classes inherit from:</p>
  
  <pre><code>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; }
  }
  </code></pre>
  
  <p>An example of one of my entity classes is:</p>
  
  <pre><code>public sealed class Record : TrackedEntity
  {
      [Key]
      public int RecordID { get; set; }
  
      [MaxLength(64)]
      public string Name { get; set; }
  }
  </code></pre>
  
  <p>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.</p>
  
  <p>So in my DbContext Class I have a DbSet for my records (and more DbSets for my other entities)</p>
  
  <pre><code>public DbSet&lt;Record&gt; Records { get; set; }
  </code></pre>
  
  <p>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.</p>
  
  <pre><code>    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 =&gt; p.PropertyType.IsGenericType 
              &amp;&amp; p.PropertyType.Name.StartsWith("DbSet") 
              &amp;&amp; p.PropertyType.GetGenericArguments().Length &gt; 0
              &amp;&amp; p.PropertyType.GetGenericArguments()[0].IsSubclassOf(typeof(TrackedEntity))))
          {
              Type type = property.PropertyType.GetGenericArguments()[0];
              modelBuilder.Entity&lt;type&gt;().Map(m =&gt; //code breaks here, I cannot use the type variable it expects a hard coded Type
              {
                  m.ToTable(type.Name + "_History");
                  m.MapInheritedProperties();
              });
          }
      }
  </code></pre>
  
  <p>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.</p>
  
  <pre><code>    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 =&gt; p.PropertyType.IsGenericType
              &amp;&amp; p.PropertyType.Name.StartsWith("DbSet")
              &amp;&amp; p.PropertyType.GetGenericArguments().Length &gt; 0
              &amp;&amp; p.PropertyType.GetGenericArguments()[0].IsSubclassOf(typeof(TrackedEntity))))
          {
              foreach (TrackedEntity entity in (DbSet&lt;TrackedEntity&gt;)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();
      }
  </code></pre>
  
  <p>Sorry for such a long question, it's quite a complicated issue. Any help would be appreciated.</p>