Entity Framework Core 与审计表
5.00/5 (1投票)
本项目描述了如何将 Entity Framework Core 与更新触发器和审计表结合使用。
引言
本文提供了一个将 Entity Framework Core 与现有 SQL Server 表结合使用的示例。这些表有一个更新触发器，它会将记录的当前版本复制到相应的审计表，并使用新的 TraceVersion 和 UTimeStamp 更新记录。
示例表
Entity Framework 类
背景
数据库中的所有表都有四个额外的列用于审计目的
UserId(int)：修改记录的用户IdDeleted(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)
一条记录插入到主表
表格 ID UserId Deleted TraceVersion UTimeStamp NTUser FName LName 活动 ABC_Users21012019-09-10 11:08:23.340gmeyerGeorgeMeyer1 -
UPDATE ABC_Users SET LName='Meyers' WHERE Id=2
当前记录（
TraceVersion=1）插入到Audit表。更新的记录获得TraceVersion=2表格 ID UserId Deleted Trace
版本UTimeStamp NTUser FName LName 活动 ABC_Users_Audit21012019-09-10 11:08:23.340gmeyerGeorgeMeyer1ABC_Users21022019-09-10 11:17:03.640gmeyerGeorgeMeyers1 -
UPDATE ABC_Users SET Deleted=1
当前记录（
TraceVersion=2）插入到Audit表。更新的记录（Deleted=1）获得TraceVersion=3并也添加到Audit表。该记录从主表中删除表格 ID UserId Deleted Trace
版本UTimeStamp NTUser FName LName 活动 ABC_Users_Audit21012019-09-10 11:08:23.340gmeyerGeorgeMeyer1ABC_Users_Audit21022019-09-10 11:17:03.640gmeyerGeorgeMeyers1ABC_Users_Audit21032019-09-10 11:17:44.020gmeyerGeorgeMeyers1ABC_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 Framework 为每个更新的 Entity 创建一个 SQL UPDATE 语句，但不会创建 SELECT 语句来检索由触发器更新的 TraceVersion 和 UTimeStamp 列。Entity Framework 为每个删除的 Entity 创建一个 SQL DELETE 语句，但在这种情况下，需要一个 UPDATE 语句将 Deleted 列设置为 1。
Using the Code
该项目是一个控制台应用程序。
安装了以下 Nuget 包
Install-Package Microsoft.Extensions.Logging.Console
Install-Package Microsoft.EntityFrameworkCore.Tools
Install-Package Microsoft.EntityFrameworkCore.SqlServer
主方法
Program.cs 中的 Main 方法插入、更新和删除记录，与上述 SQL 语句完全相同，但使用 Entity 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 类中。它包含一个 DbSet 的 TVUT，以便可以编写查询来检索 TraceVersion 和 UTimeSTamp。
public partial class Model1
{
public DbSet<tvut> TVUTs { get; set; }
...
}
OnModelCreatingPartial
在此方法中，设置实体的特殊属性。TVUT 实体被标记为没有键，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
当 Scaffold-DbContext 在没有 -UseDatabaseNames 选项的情况下运行时，Entity Framework 通过删除下划线字符并将除第一个字符之外的所有字符转换为小写来从表名创建实体类。此函数用于检索给定实体对象的表名。
private string GetTableName(object entity)
{
var entityType = Model.FindEntityType(entity.GetType());
return entityType.GetTableName();
}
SaveChanges
方法 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。感谢文章 Entity Framework Core: Soft Delete。这种情况类似于软删除，但记录从主表移动到审计表。 - 创建一个新事务。
- 调用基类的
SaveChanges。 - 对于每个修改过的实体，构造一个 SQL 语句来检索
TraceVersion和UTimeStamp。SQL 语句在DbSet TVUTs 上使用FromSqlRaw执行。检索到值后，将其分配给实体。由于重新加载这两个值，需要事务。在base.SaveChanges()结束和TVUTs.FromSqlRaw(sql)开始之间，其他人可能会更新实体。 - 对于每个已删除的实体，其
State更改为Detached，因此它从Model1.中删除
摘要
本项目演示了可以创建一个 Entity Framework Model，该模型
- 确保所有新的（添加的）实体都获得
Deleted=false、TraceVersion=1和UTimeStamp=当前日期和时间。 - 为所有更新的实体重新加载由触发器给出的
TraceVersion和UTimeStamp列。 - 将所有删除更改为
Deleted=1的更新，并在保存后分离这些实体。
历史
- 2020 年 4 月 16 日：初始版本 - 表
Abc_Users的模型，update重新加载TraceVersion和UTimeStamp，使用TraceVersion列的乐观并发。