Entity Framework core captures database changes

Form writing habits together ！ This is my participation 「 Nuggets day new plan · 4 Yuegengwen challenge 」 Of the 21 God , Click to see the event details .

In actual projects, we often need to record the changes of data stored in the database （ For example, record the original value of the data before modifying the data ）, In this way, the data can be restored to the state before the change in case of misoperation , It can also be traced back to the person who modified the data . Most developers define their own code to record data changes , But this not only takes time and effort, but also affects the performance of the business . Of course , We can also use database triggers to record these operations , stay SQL Server database 2017 The above version provides us with the function of tracking database data changes , Using this function can accurately record the changes of database data . Although this function is powerful, sometimes the database we use is not SQL Server database , Or in some cases we are not suitable to use SQL Server This function provided by database . So what to do at this time ？ If you're using Entity Framework Core 2.0 And above to develop the project , Then the problem will be solved . stay Entity Framework Core in , As long as the data change record is captured , We can restore the data to the state before the change at any time , Here the database change record is called audit data . So let's look at two questions first ：

1. When is the audit data generated and written to the database ？
2. How are the old and new values of data obtained ？

To answer these two questions , Then follow me to see how to use Entity Framework Core To capture audit data .

zero 、 Create an audit model

The first step in capturing audit data and storing it in a database is to create an audit model , Only audit data with audit model can be stored in database correctly .

public class Audit
{
    public int Id { get; set; }
    public string TableName { get; set; }
    public DateTime DateTime { get; set; }
    [NotMapped]
    public Operation Operation { get; set; }
    public string OperationString
    {
        get { return Operation.ToString(); }
        private set { Operation = (Operation)Enum.Parse(typeof(Operation), value, true); }
    }
    public string Key { get; set; }
    public string Old { get; set; }
    /// <summary>
    ///  Data after operation 
    /// </summary>
    public string New { get; set; }
}
/// <summary>
///  Operation type 
/// </summary>
public enum Operation
{
    Add = 0,
    Delete = 1,
    Modified = 2
}
 Copy code 

The audit model created by the above code contains the name of the table to be operated TableName 、 Type of operation Operation 、 Primary key of the operated data Key 、 Data before operation Old And the data after the operation New , The operation type includes addition, deletion and modification .

One 、 Create audit data store

Now we have the audit model , But audit model is not the only way , We also need to create classes related to storing audit data , Let's create this class together .

public class AuditDb
{
    public EntityEntry _entityEntry { get; set; }
    public AuditDb(EntityEntry entityEntry)
    {
        this._entityEntry = entityEntry;
    }
    public string TableName { get; set; }
    public Operation Operation { get; set; }
    public Dictionary<string, object> keys { get; } = new Dictionary<string, object>();
    public Dictionary<string, object> olds { get; } = new Dictionary<string, object>();
    public Dictionary<string, object> news { get; } = new Dictionary<string, object>();
    public List<PropertyEntry> propertyEntries { get; } = new List<PropertyEntry>();
    public bool HasPropertyEntries => propertyEntries.Any();
    public Audit ToAudit()
    {
        Audit audit = new Audit
        {
            TableName = TableName,
            Operation = Operation,
            DateTime = DateTime.Now,
            Key = JsonConvert.SerializeObject(keys),
            Old = olds.Count == 0 ? null : JsonConvert.SerializeObject(olds),
            New = news.Count == 0 ? null : JsonConvert.SerializeObject(news)
        };
        return audit;
    }
}
 Copy code 

This class is mainly used to store table names , Primary key of the operated data Id, Data before being operated and data after being operated . Our primary key will be seen in our operation code above Id、 The variables of the data before and after the operation are defined as dictionary types , This is because there may be problems with batch operation in our program . After converting the above information into Audit Prompt us to judge the length of the data before and after the operation , This is because when we add new data, there is no old data , When we submit data without making any changes to the data, there is no new data .

Two 、 rewrite SaveChanges

This example rewrites SaveChanges , about SaveChangesAsync The same applies . We need to be in OnBeforSaveBehavior Create in method AuditDb list .

public class EFContext : DbContext
{
    public override int SaveChanges(bool acceptAllChangesOnSuccess)
    {
        List<AuditDb> auditDbs = OnBeforeSaveBehavior();
        var result = base.SaveChanges(acceptAllChangesOnSuccess);
        return result;
    }
    List<AuditDb> OnBeforeSaveBehavior()
    {
        ChangeTracker.DetectChanges();
        List<AuditDb> auditDbs = new List<AuditDb>();
        foreach (EntityEntry entity in ChangeTracker.Entries())
        {
            if (entity.Entity is Audit || entity.State == EntityState.Detached || entity.State == EntityState.Unchanged)
            {
                continue;
            }
            AuditDb auditDb = new AuditDb(entity)
            {
                TableName = entity.Metadata.Name
            };
            auditDbs.Add(auditDb);
            foreach (var property in entity.Properties)
            {
                if (property.IsTemporary)
                {
                    auditDb.propertyEntries.Add(property);
                    continue;
                }
                var propertName = property.Metadata.Name;
                if (property.Metadata.IsPrimaryKey())
                {
                    auditDb.keys[propertName] = property.CurrentValue;
                    continue;
                }
                switch (entity.State)
                {
                    case EntityState.Deleted:
                        auditDb.Operation = Operation.Delete;
                        auditDb.olds[propertName] = property.OriginalValue;
                        break;
                    case EntityState.Modified:
                        if (property.IsModified)
                        {
                            auditDb.Operation = Operation.Modified;
                            auditDb.olds[propertName] = property.OriginalValue;
                            auditDb.news[propertName] = property.CurrentValue;
                        }
                        break;
                    case EntityState.Added:
                        auditDb.Operation = Operation.Add;
                        auditDb.news[propertName] = property.CurrentValue;
                        break;
                }
            }
        }
        List<Audit> audits = new List<Audit>();
        foreach (var item in auditDbs.Where(p => !p.HasPropertyEntries))
        {
            audits.Add(item.ToAudit());
        }
        return auditDbs.Where(p => p.HasPropertyEntries).ToList();
    }
}
 Copy code 

up to now , All code to capture audit data is complete , One thing to note here is that some entity properties are generated by the database , For example, the current date 、Id etc. , These values need to wait SaveChanges After the method is executed, you can get , That is to say, in this case, the audit data must be saved in SaveChanges After method .

3、 ... and 、 summary

Through the previous code example and explanation , We can then answer the two questions raised earlier , Except when part of the data is automatically generated by the database , Most of the time, I call SaveChanges Before method , We go through... In the context ChangeTracker Property to get old and new values and save . The above code is easy to understand , For the most part , It can be used directly in the project .