带有审计表的实体框架核心(EF Core)

最新推荐文章于 2024-01-02 12:02:42 发布

寒冰屋

最新推荐文章于 2024-01-02 12:02:42 发布

阅读量1.2k

点赞数

分类专栏： ASP.NET CORE 架构及框架文章标签： EF Core 审计表

原文链接：https://www.codeproject.com/Articles/5265213/Entity-Framework-Core-with-Audit-Tables

版权

ASP.NET CORE 同时被 2 个专栏收录

1136 篇文章 55 订阅

订阅专栏

架构及框架

635 篇文章 16 订阅

订阅专栏

OnModelCreatingPartial

GetTableName

保存更改

摘要

下载源代码5.2 KB

介绍

本文提供了将Entity Framework Core与现有SQL Server表一起使用的示例。这些表具有更新触发器，该触发器将记录的当前版本复制到相应的审计表，并使用新的TraceVersion和UTimeStamp更新记录。

示例表：

实体框架类：

背景

数据库中的所有表都有四个附加列用于审计目的：

UserId（int）：修改记录的用户Id
Deleted（bit）：指示是否删除记录
TraceVersion（int）：记录的版本号
UTimeStamp（datetime）：上次修改的日期和时间

SQL操作执行以下操作：

INSERT：Insert上没有触发器，记录将按原样插入到表中。数据访问层可确保Deleted=0，TraceVersion=1和UTimeStamp=当前日期和时间。
UPDATE：有一个AFTER UPDATE触发器。如果
- Deleted=0：将表中的当前记录插入到审计表中，然后更新当前记录，将TraceVersion自增加1，并将UTimeStamp设置为当前日期和时间。
- Deleted=1：与Deleted=0一样，但除此之外，更新的记录（带有Deleted=1）也插入到审计表中，并从主表中删除。
DELETE：AFTER DELETE触发器禁止该DELETE语句。记录的删除必须通过将Deleted列更新为1（如软删除）来完成。

例如，以下语句将在数据库中产生以下记录：

INSERT ABC_Users(UserId,Deleted,TraceVersion,UTimeStamp,NTUser,FName,LName,Active) 
VALUES(1,0,1,GETDATE(),'gmeyer','George','Meyer',1)

将一条记录插入到主表中：

Table	Id	UserId	Deleted	TraceVersion	UTimeStamp	NTUser	FName	LName	Active
ABC_Users	2	1	0	1	2019-09-10 11:08:23.340	gmeyer	George	Meyer	1

UPDATE ABC_Users SET LName='Meyers' WHERE Id=2

当前记录（带有TraceVersion=1）被插入到Audit表中。更新后的记录为TraceVersion=2：

Table	Id	UserId	Deleted	Trace Version	UTimeStamp	NTUser	FName	LName	Active
ABC_Users_Audit	2	1	0	1	2019-09-10 11:08:23.340	gmeyer	George	Meyer	1
ABC_Users	2	1	0	2	2019-09-10 11:17:03.640	gmeyer	George	Meyers	1

UPDATE ABC_Users SET Deleted=1

当前记录（带有TraceVersion=2）被插入到Audit表中。更新的记录（带有Deleted=1）将获取TraceVersion=3并也添加到Audit表中。该记录将从主表中删除：

Table	Id	UserId	Deleted	Trace Version	UTimeStamp	NTUser	FName	LName	Active
ABC_Users_Audit	2	1	0	1	2019-09-10 11:08:23.340	gmeyer	George	Meyer	1
ABC_Users_Audit	2	1	0	2	2019-09-10 11:17:03.640	gmeyer	George	Meyers	1
ABC_Users_Audit	2	1	0	3	2019-09-10 11:17:44.020	gmeyer	George	Meyers	1

ABC_Users中没有记录。

下面是创建表和触发器以及插入管理员用户的SQL语句：

DROP TABLE IF EXISTS ABC_Users
GO
CREATE TABLE [dbo].[ABC_Users](
    [Id] [int] IDENTITY(1,1) NOT NULL PRIMARY KEY,
    [UserId] [int] NOT NULL,
    [Deleted] [bit] NOT NULL,
    [TraceVersion] [int] NOT NULL,
    [UTimeStamp] [datetime] NOT NULL,
    [NTUser] [varchar](50) NOT NULL,
    [FName] [varchar](20) NOT NULL,
    [LName] [varchar](50) NOT NULL,
    [Active] [bit] NOT NULL,
 CONSTRAINT [IX_ABC_Users] UNIQUE NONCLUSTERED ([NTUser] ASC) ON [PRIMARY]
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[ABC_Users]  WITH CHECK ADD  CONSTRAINT [FK_ABC_Users_ABC_Users] _
                               FOREIGN KEY([UserId])
REFERENCES [dbo].[ABC_Users] ([Id])
GO

DROP TABLE IF EXISTS ABC_Users_Audit
GO
CREATE TABLE [dbo].[ABC_Users_Audit](
    [Id] [int] NOT NULL,
    [UserId] [int] NOT NULL,
    [Deleted] [bit] NOT NULL,
    [TraceVersion] [int] NOT NULL,
    [UTimeStamp] [datetime] NOT NULL,
    [NTUser] [varchar](50) NOT NULL,
    [FName] [varchar](20) NOT NULL,
    [LName] [varchar](50) NOT NULL,
    [Active] [bit] NOT NULL,
 CONSTRAINT [PK_ABC_Users_Audit] PRIMARY KEY CLUSTERED ([Id] ASC, 
            [TraceVersion] ASC) ON [PRIMARY]) ON [PRIMARY]
GO

---------- AUDIT TRIGGER SCRIPT FOR TABLE ABC_Users---------------
CREATE TRIGGER [dbo].[trABC_Users_AUDIT_UD] ON [dbo].[ABC_Users]
AFTER UPDATE, DELETE
AS
/* If no rows were affected, do nothing */
IF @@ROWCOUNT=0
    RETURN

SET NOCOUNT ON
BEGIN TRY
    DECLARE @Counter INT, @Now DATETIME
    SET @Now = GETDATE()
    /* Check the action (UPDATE or DELETE) */
    SELECT @Counter = COUNT(*)
    FROM INSERTED
    IF @Counter = 0 -->; DELETE
        THROW 50000, 'DELETE action is prohibited for ABC_Users', 1

    /* Insert previous record to Audit */
    INSERT INTO ABC_Users_Audit([Id],[UserId],[Deleted], _
                [TraceVersion],[UTimeStamp],[NTUser],[FName],[LName],[Active])  
    SELECT d.[Id],d.[UserId],d.[Deleted],d.[TraceVersion],_
                  d.[UTimeStamp],d.[NTUser],d.[FName],d.[LName],d.[Active]
    FROM DELETED d

    /* Update master record TraceVersion, UTimeStamp */
    UPDATE main
    SET main.TraceVersion = d.TraceVersion + 1, main.UTimeStamp = @Now
    FROM ABC_Users main
    INNER JOIN DELETED d ON d.Id = main.Id
    INNER JOIN INSERTED i ON i.Id = main.Id

    /* Process deleted rows */
    IF NOT EXISTS(SELECT 1 FROM INSERTED WHERE Deleted = 1)
        RETURN
    /* Re-insert last updated master record into Audit table where Deleted = 1 */
    INSERT INTO ABC_Users_Audit([Id],[UserId],[Deleted],[TraceVersion],_
                                [UTimeStamp],[NTUser],[FName],[LName],[Active])  
    SELECT d.[Id],d.[UserId],d.[Deleted],d.[TraceVersion],d.[UTimeStamp],_
                             d.[NTUser],d.[FName],d.[LName],d.[Active]
    FROM ABC_Users d
    INNER JOIN INSERTED i ON d.Id = i.Id
    WHERE i.Deleted = 1

    /* Delete master record */
    DELETE c
    FROM ABC_Users c
    INNER JOIN INSERTED i ON c.Id = i.Id
    WHERE i.Deleted = 1
END TRY
BEGIN CATCH
    THROW
END CATCH
GO

ALTER TABLE [dbo].[ABC_Users] ENABLE TRIGGER [trABC_Users_AUDIT_UD]
GO

INSERT ABC_Users(UserId,Deleted,TraceVersion,UTimeStamp,NTUser,FName,LName,Active)
VALUES(1,0,1,GETDATE(),'admin','Admin','Admin',1)

实体框架为每个更新的Entity创建一个SQL UPDATE语句，但不创建一个SELECT语句来检索由触发更新的TraceVersion和UTimeStamp。实体框架为每个已删除的Entity创建一条SQL DELETE语句，但是在这种情况下，需要使用一条UPDATE语句将列Deleted设置为1。

使用代码

该项目是一个控制台应用程序。

已安装以下Nuget软件包：

Install-Package Microsoft.Extensions.Logging.Console
Install-Package Microsoft.EntityFrameworkCore.Tools
Install-Package Microsoft.EntityFrameworkCore.SqlServer

主(Main)方法

Program.cs中的Main方法完全按照上述语句插入、更新和删除记录，但是使用：SQLEntity Framework

static void Main(string[] args)
{
    try
    {
        AbcUsers user;
        var optionsBuilder =
            new DbContextOptionsBuilder<model1>()
            .UseSqlServer(GetConnectionString())
            .UseLoggerFactory(LoggerFactory.Create(builder => builder.AddConsole()));
        Console.WriteLine("Adding user");
        using (var context = new Model1(optionsBuilder.Options))
        {
            var dateNow = DateTime.Now;
            user = new AbcUsers()
            {
                UserId = 1,
                Ntuser = "gmeyer",
                Fname = "George",
                Lname = "Meyer",
                Active = true
            };
            context.AbcUsers.Add(user);
            context.SaveChanges();
            Console.WriteLine("user.Id={0}", user.Id);
            WriteChangeTrackerCount(context);
        }
        Console.WriteLine("Updating user");
        using (var context = new Model1(optionsBuilder.Options))
        {
            context.AbcUsers.Attach(user);
            user.Lname = "Meyers";
            context.SaveChanges();
            Console.WriteLine("user.TraceVersion={0}", user.TraceVersion);
            WriteChangeTrackerCount(context);
        }
        Console.WriteLine("Deleting user");
        using (var context = new Model1(optionsBuilder.Options))
        {
            context.AbcUsers.Attach(user);
            context.AbcUsers.Remove(user);
            context.SaveChanges();
            Console.WriteLine("context.Entry(user).State={0}", context.Entry(user).State);
            WriteChangeTrackerCount(context);
        }
        Console.WriteLine("Test ok");
    }
    catch (Exception ex)
    {
        Console.WriteLine("Test not ok");
        Console.WriteLine(ex.ToString());
    }
    Console.WriteLine("Press any key to close");
    Console.ReadKey();
}

运行程序

若要运行该程序，应在SQL Server上创建一个数据库，并在该数据库中，使用CreateTables.sql脚本中给定的SQL脚本创建两个表。应该在Program.cs方法GetConnectionString中相应地修改连接字符串。在提供的连接字符串中，数据库称为DB1。运行项目应创建以下输出：

Adding user
info: Microsoft.EntityFrameworkCore.Infrastructure[10403]
      Entity Framework Core 3.1.3 initialized 'Model1' _
      using provider 'Microsoft.EntityFrameworkCore.SqlServer' with options: None
info: Microsoft.EntityFrameworkCore.Database.Command[20101]
      Executed DbCommand (77ms) [Parameters=[@p0='?' (DbType = Boolean), _
      @p1='?' (DbType = Boolean), @p2='?' (Size = 20) (DbType = AnsiString), _
      @p3='?' (Size = 50) (DbType = AnsiString), @p4='?' _
      (Size = 50) (DbType = AnsiString), @p5='?' (DbType = Int32), _
      @p6='?' (DbType = Int32), @p7='?' (DbType = DateTime)], _
      CommandType='Text', CommandTimeout='30']
      SET NOCOUNT ON;
      INSERT INTO [ABC_Users] ([Active], [Deleted], [FName], [LName], _
                  [NTUser], [TraceVersion], [UserId], [UTimeStamp])
      VALUES (@p0, @p1, @p2, @p3, @p4, @p5, @p6, @p7);
      SELECT [Id]
      FROM [ABC_Users]
      WHERE @@ROWCOUNT = 1 AND [Id] = scope_identity();
user.Id=2
ChangeTracker.Entries().ToList().Count=1
Updating user
info: Microsoft.EntityFrameworkCore.Infrastructure[10403]
      Entity Framework Core 3.1.3 initialized 'Model1' _
      using provider 'Microsoft.EntityFrameworkCore.SqlServer' with options: None
info: Microsoft.EntityFrameworkCore.Database.Command[20101]
      Executed DbCommand (181ms) [Parameters=[@p1='?' (DbType = Int32), _
      @p0='?' (Size = 50) (DbType = AnsiString), @p2='?' (DbType = Int32)], _
      CommandType='Text', CommandTimeout='30']
      SET NOCOUNT ON;
      UPDATE [ABC_Users] SET [LName] = @p0
      WHERE [Id] = @p1 AND [TraceVersion] = @p2;
      SELECT @@ROWCOUNT;
info: Microsoft.EntityFrameworkCore.Database.Command[20101]
      Executed DbCommand (3ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
      SELECT TraceVersion, UTimeStamp FROM ABC_Users WHERE Id=2
user.TraceVersion=2
ChangeTracker.Entries().ToList().Count=1
Deleting user
info: Microsoft.EntityFrameworkCore.Infrastructure[10403]
      Entity Framework Core 3.1.3 initialized 'Model1' _
      using provider 'Microsoft.EntityFrameworkCore.SqlServer' with options: None
info: Microsoft.EntityFrameworkCore.Database.Command[20101]
      Executed DbCommand (27ms) [Parameters=[@p1='?' (DbType = Int32), _
      @p0='?' (DbType = Boolean), @p2='?' (DbType = Int32)], _
      CommandType='Text', CommandTimeout='30']
      SET NOCOUNT ON;
      UPDATE [ABC_Users] SET [Deleted] = @p0
      WHERE [Id] = @p1 AND [TraceVersion] = @p2;
      SELECT @@ROWCOUNT;
context.Entry(user).State=Detached
ChangeTracker.Entries().ToList().Count=0
Test ok
Press any key to close

IAudited

接口IAudited由所有实体实现。它定义了所有实体都有列Id，Deleted，TraceVersion和UTimeStamp。

interface IAudited
{
    int Id { get; set; }
    bool Deleted { get; set; }
    int TraceVersion { get; set; }
    DateTime UTimeStamp { get; set; }
}

Model1.cs

实体框架模型Model1.cs是使用以下命令在Nuget软件包管理器控制台中创建的：

Scaffold-DbContext 'data source=localhost;initial catalog=DB1;
integrated security=True;' Microsoft.EntityFrameworkCore.SqlServer 
-Context Model1 -F -DataAnnotations -Tables ABC_Users

上面的命令中的连接字符串可能需要调整，但是不必再次运行此命令。

Model1.Partial.cs

由Scaffold-DbContext生成的类的自定义代码可以放在此处。

部分类CUsers

每个审计的表必须实现接口IAudited：

public partial class CUsers : IAudited { }

对于每个表，必须添加与上面相似的行。

TVUT类

此类包含字段TraceVersion和UTimeStamp。它用于在更新语句后重新加载这两个值。

public class TVUT
{
    public int TraceVersion { get; set; }
    public DateTime UtimeStamp { get; set; }
}

局部类Model1

该Model1类由Scaffold-DbContext命令生成。与该类有关的任何自定义代码都放置在partial类中。它具有TVUT的DbSet，因此可以编写查询来检索TraceVersion和UTimeSTamp。

public partial class Model1
{
    public DbSet<tvut> TVUTs { get; set; }
    ...
}

OnModelCreatingPartial

在这个方法中，设置实体的特殊属性。该TVUT实体被标记为没有key，并且该AbcUsers实体的TraceVersion字段设置为并发令牌。这意味着，该字段被添加到UPDATE和DELETE语句的WHERE子句中，例如：

UPDATE [dbo].[CUsers]
SET [LName] = @0
WHERE (([Id] = @1) AND ([TraceVersion] = @2))

这种方式实现了乐观并发。

partial void OnModelCreatingPartial(ModelBuilder modelBuilder)
{
    modelBuilder.Entity<tvut>(e => e.HasNoKey());
    modelBuilder.Entity<abcusers>(entity => entity.Property
                 (e => e.TraceVersion).IsConcurrencyToken(true));
}

对于每个表，AbcUsers必须将与上述实体行相似的行添加到该OnModelCreatingPartial函数中。

GetTableName

如果在不带该选项-UseDatabaseNames的情况下运行Scaffold-DbContext，则Entity Framework通过删除下划线字符并将除第一个字符外的所有字符转换为小写字母，从而从表名创建实体类。此函数用于检索给定实体对象的表名。

private string GetTableName(object entity)
{
    var entityType = Model.FindEntityType(entity.GetType());
    return entityType.GetTableName();
}

保存更改

方法SaveChanges被覆盖。

public override int SaveChanges()
{
    var entriesAudited = ChangeTracker.Entries().Where(e => e.Entity is IAudited);
    var entriesAdded = entriesAudited.Where(e => e.State == EntityState.Added).ToList();
    var entriesModified = entriesAudited.Where(e => e.State == EntityState.Modified).ToList();
    var entriesDeleted = entriesAudited.Where(e => e.State == EntityState.Deleted).ToList();
    foreach (var item in entriesAdded)
    {
        var entity = (IAudited)item.Entity;
        (entity.Deleted, entity.TraceVersion, entity.UtimeStamp) = (false, 1, DateTime.Now);
    }
    foreach (var item in entriesDeleted)
    {
        item.State = EntityState.Unchanged;
        ((IAudited)item.Entity).Deleted = true;
    }
    var rowCount = 0;
    using (var scope = new TransactionScope())
    {
        rowCount = base.SaveChanges();
        foreach (var item in entriesModified)
        {
            var entity = (IAudited)item.Entity;
            var sql = $"SELECT TraceVersion, _
                      UTimeStamp FROM {GetTableName(entity)} WHERE Id={entity.Id}";
            var tu = TVUTs.FromSqlRaw(sql).ToList()[0];
            (entity.TraceVersion, entity.UtimeStamp) = (tu.TraceVersion, tu.UtimeStamp);
        }
        scope.Complete();
    }
    if (rowCount > 0)
        foreach (var item in entriesDeleted)
            item.State = EntityState.Detached;
    return rowCount;
}

检索审计的条目。
对于在审计项目，每个加入的实体，字段Deleted，TraceVersion和UTimeStamp被填充。
对于审计条目中每个已删除的实体，将实体设置为不变，然后将该Deleted字段设置为1。这种情况类似于软删除，但是记录从主表移至审计表。
创建一个新事务。
基类的SaveChanges 调用。
对于每个修改的实体，都会构造一条SQL语句来检索TraceVersion和UTimeStamp。在DbSet TVUT上使用FromSqlRaw执行SQL语句。检索到后，会将值分配给实体。由于这两个值的重新加载，因此需要事务。其他人可以在base.SaveChanges()的结束和TVUTs.FromSqlRaw(sql)开始之间更新实体。
对于每个删除的实体，其State更改为Detached，因此将其从Model1中删除。