实体框架快照历史记录 [英] Entity Framework Snapshot History
问题描述
我试图弄清楚如何使用Code First Entity Framework并保留某些表的快照历史记录。这意味着对于我要跟踪的每个表，我都希望有一个重复的表以_History为后缀。每次更改跟踪表行时，都会将数据库中的数据复制到历史记录表中，然后再将新数据保存到原始表中，并且Version列会增加。
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.
因此，假设我有一个名为Record的表。我有一行（ID：1，Name：One，Version：1）。当我将其更改为（ID1：Name：Changed，Version：2）时，Record_History表将获得一行（ID：1，Name：One，Version：1）。
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).
我看到了很多很好的例子，并且知道周围有很多库可以使用Entity Framework来记录更改的审核日志，但是我需要在每个修订版中提供完整的实体快照以进行SQL报告。
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.
在C＃中，我有一个基类，所有与 Tracked表等效的实体类都继承自该基类：
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; }
}
现在我要坚持的部分。我想避免为我创建的每个实体输入和维护一个单独的_History类。我想做些聪明的事来告诉我的DbContext类，它拥有的每个具有从TrackedEntity继承的类型的DbSet都应该有一个历史记录对应表，并且只要保存了该类型的实体，就应该将原始值从数据库复制到数据库中。历史表。
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.
因此，在我的DbContext类中，我有一个DbSet作为记录（还有其他实体的DbSet）
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; }
我重写了OnModelCreating方法，因此可以为新的_History表注入映射。但是，我无法弄清楚如何使用反射将每个实体的类型传递给DbModelBuilder。
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();
});
}
}
我甚至不确定是否使用像这甚至会生成新的历史记录表。我也不知道如何处理保存，不确定实体映射是否意味着更改然后保存在两个表上？我可以在DbContext中创建一个SaveChanges方法，该方法可以遍历我的实体，但是我不知道如何将实体保存到第二个表中。
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; }
}
最后，我在上下文类中创建了SaveChanges方法的重载，这
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);
}
..........
}
然后，每当我保存更改时，我都会使用新方法，并传入当前用户名。我希望避免使用_History类，因为它们需要与主要实体类一起维护，并且容易忘记。
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.
这篇关于实体框架快照历史记录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
