实体框架6(代码优先)实体版本控制和审计 [英] Entity Framework 6 (code first) entity versioning and auditing
问题描述
我正在使用Entity Framework 6.1.1与SQL Server 2008 R2。
I'm looking at using Entity Framework 6.1.1 with SQL Server 2008 R2.
目前,我正在使用代码优先的EF功能创建我的模型和数据库。我的基本用例是创建一个对特定实体的所有更改的日志( ID
是关键列),以帮助审计人员跟踪所做的所有更改以及由谁执行的操作。例如:
Currently I'm creating my models and database using the code-first EF feature. My basic use-case is to create a journal of all changes to a particular entity (ID
is the key column) to help auditors track all changes made and by whom. e.g:
|ID|Version|Created Date|Created By|Modified Date|Modified By|Modify Action| ... (rest of entity fields)
-------------------------------------------------------------------------------------------------------
| 4| 12 | 12-Mar-14 | tom | 20-Feb-15 | jack | Update |
| 4| 11 | 12-Mar-14 | tom | 14-Feb-15 | jill | Update |
| 4| 1 | 12-Mar-14 | tom | 12-Mar-14 | tom | Create |
实体框架是否支持此类型的数据库方案?如果是这样,我如何设置我的模型/解决方案来促进这一点?
Does Entity Framework support this type of database scheme? If so, how can I set my models/solution up to facilitate this?
另一种替代方法是通过拦截所有调用 SaveChanges ()
方法在$ code> DbContext 并将所有数据库更改记录到单独的审核
表中,但这可能使得检索信息更具挑战性。
The other alternative I have is by intercepting all calls to the SaveChanges()
method on the DbContext
and log all database changes into a separate Audit
table, but this might make retrieving information more challenging.
任何有关使用SQL Server和EF 6创建审计跟踪的帮助将受到青睐。
Any help on creating audit trails with SQL Server and EF 6 would be greately appreciated.
推荐答案
我使用了你提到的第二种方法,通过重载dbContext SaveChanges()方法:
I have used the 2nd approach you mention, by overloading the dbContext SaveChanges() method:
public class MyContext : DbContext
{
public int SaveChanges(int userId)
{
// Get all Added/Deleted/Modified entities (not Unmodified or Detached)
foreach (var ent in this.ChangeTracker.Entries().Where(p => p.State == EntityState.Added
|| p.State == EntityState.Deleted || p.State == EntityState.Modified))
{
foreach (AuditLog x in GetAuditRecordsForChange(ent, userId))
{
this.AuditLogs.Add(x);
}
}
return base.SaveChanges();
}
...
所以如果我要记录一个特定的实体,我只是调用重载的SaveChanges&传递一个UserId:
So if I want to log a particular entity, I just call the overloaded SaveChanges & pass in a UserId:
public void Update(StockCatalogueItem entity, int userId)
{
_context.SaveChanges(userId);
}
我还有一个自定义 DoNotLog
属性,我用来装饰我不想记录的实体属性。没有这个,记录可以生成大量的数据,因为每个实体修改等于一个db条目。
I also have a custom DoNotLog
attribute which I use to decorate the entity properties that I don't want to log. Without this, the logging could generate a huge amount of data, as each entity modification equals one db entry.
[DoNotLog]
public int CreatedBy { get; set; }
GetAuditRecordsForChange
任何 DoNotLog
属性,并返回保存在AuditLogs表中的列表< AuditLog>
The GetAuditRecordsForChange
method does the checking for any DoNotLog
properties and returns a List<AuditLog>
which gets saved in the AuditLogs table:
public class AuditLog
{
public int Id { get; set; }
public int CreatedBy { get; set; }
public DateTime CreatedOn { get; set; }
public AuditEventType EventType { get; set; }
public string TableName { get; set; }
public int EntityId { get; set; }
public string ColumnName { get; set; }
public string Controller { get; set; }
public string Action { get; set; }
public string IPAddress { get; set; }
public string OriginalValue { get; set; }
public string NewValue { get; set; }
}
这篇关于实体框架6(代码优先)实体版本控制和审计的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!