问题描述

我正在考虑将 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      |

Entity Framework 是否支持这种类型的数据库方案?如果是这样，我该如何设置我的模型/解决方案来促进这一点?

Does Entity Framework support this type of database scheme? If so, how can I set my models/solution up to facilitate this?

我的另一种选择是拦截对 DbContext 上的 SaveChanges() 方法的所有调用，并将所有数据库更改记录到单独的 Audit table，但这可能会使检索信息更具挑战性.

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 属性，用于装饰我不想记录的实体属性.如果没有这个，日志记录可能会生成大量数据，因为每个实体修改都等于一个数据库条目.

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 表中的 List:

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; }
    }

这篇关于Entity Framework 6(代码优先)实体版本控制和审计的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持编程学习网！