带审计表的Entity Framework
5.00/5 (8投票s)
本项目描述了如何将 Entity Framework 与更新触发器和审计表结合使用。
引言
本文提供了将 Entity Framework 6 与现有 SQL Server 表结合使用的示例。这些表有一个更新触发器，它将记录的当前版本复制到各自的审计表，并使用新的 TraceVersion 和 UTimeStamp 更新记录。
背景
数据库中的所有表都有 4 个额外的列用于审计目的
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 CUsers(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 活动 CUsers21012019-09-10 11:08:23.340gmeyerGeorgeMeyer1 -
UPDATE CUsers SET LName='Meyers' WHERE Id=2
当前记录（TraceVersion=1）插入到Audit表。更新后的记录获得TraceVersion=2表格 ID UserId Deleted TraceVersion UTimeStamp NTUser FName LName 活动 CUsers_Audit21012019-09-10 11:08:23.340gmeyerGeorgeMeyer1CUsers21022019-09-10 11:17:03.640gmeyerGeorgeMeyers1 -
UPDATE CUsers SET Deleted=1
当前记录（TraceVersion=2）插入到Audit表。更新后的记录（Deleted=1）获得TraceVersion=3，也添加到Audit表。记录从主表中删除表格 ID UserId Deleted TraceVersion UTimeStamp NTUser FName LName 活动 CUsers_Audit21012019-09-10 11:08:23.340gmeyerGeorgeMeyer1CUsers_Audit21022019-09-10 11:17:03.640gmeyerGeorgeMeyers1CUsers_Audit21032019-09-10 11:17:44.020gmeyerGeorgeMeyers1CUsers中没有记录。
创建表、触发器和插入管理用户的 SQL 语句如下
CREATE TABLE [dbo].[CUsers](
[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_CUsers] UNIQUE NONCLUSTERED ([NTUser] ASC) ON [PRIMARY]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[CUsers] WITH CHECK ADD CONSTRAINT [FK_CUsers_CUsers] FOREIGN KEY([UserId])
REFERENCES [dbo].[CUsers] ([Id])
GO
CREATE TABLE [dbo].[CUsers_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_CUsers_Audit] PRIMARY KEY CLUSTERED ([Id] ASC, _
[TraceVersion] ASC) ON [PRIMARY]) ON [PRIMARY]
GO
---------- AUDIT TRIGGER SCRIPT FOR TABLE CUsers---------------
CREATE TRIGGER [dbo].[trCUsers_AUDIT_UD] ON [dbo].[CUsers]
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 CUsers', 1
/* Insert previous record to Audit */
INSERT INTO CUsers_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 CUsers 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 CUsers_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 CUsers d
INNER JOIN INSERTED i ON d.Id = i.Id
WHERE i.Deleted = 1
/* Delete master record */
DELETE c
FROM CUsers 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].[CUsers] ENABLE TRIGGER [trCUsers_AUDIT_UD]
GO
INSERT CUsers(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
该项目是一个控制台应用程序。
主方法
Program.cs 中的 Main 方法插入、更新和删除记录，与上面的 SQL 语句完全相同，但使用 Entity Framework
static void Main(string[] args)
{
try
{
int id;
CUser user;
var connString =
ConfigurationManager.ConnectionStrings["DB1Entities"].ConnectionString;
Console.WriteLine("Connection string={0}", connString);
Console.WriteLine("Adding user");
using (var context = new DB1Entities())
{
context.Database.Log = Console.WriteLine;
var dateNow = DateTime.Now;
user = new CUser()
{
UserId = 1,
NTUser = "gmeyer",
FName = "George",
LName = "Meyer",
Active = true
};
context.CUsers.Add(user);
context.SaveChanges();
id = user.Id;
Console.WriteLine("user.Id={0}", user.Id);
WriteChangeTrackerCount(context);
}
using (var context = new DB1Entities())
{
context.Database.Log = Console.WriteLine;
context.CUsers.Attach(user);
user.LName = "Meyers";
context.SaveChanges();
Console.WriteLine("user.TraceVersion={0}", user.TraceVersion);
WriteChangeTrackerCount(context);
}
using (var context = new DB1Entities())
{
context.Database.Log = Console.WriteLine;
context.CUsers.Attach(user);
context.CUsers.Remove(user);
context.SaveChanges();
Console.WriteLine
("context.Entry(user).State={0}", context.Entry(user).State);
WriteChangeTrackerCount(context);
}
}
catch (Exception ex)
{
Console.WriteLine(ex.ToString());
}
Console.ReadKey();
}
运行程序
要运行程序，应在 SQL Server 上创建一个数据库，并在该数据库中使用先前给出的 SQL 脚本创建两个表。连接字符串应在 app.config 中相应修改。在提供的连接字符串中，数据库名为 DB1。运行项目应创建以下输出
Connection string=metadata=res://*/Model1.csdl|res://*/Model1.ssdl|res://*/Model1.msl;provider=System.Data.SqlClient;provider connection string="data source=localhost;initial catalog=DB1;integrated security=True;MultipleActiveResultSets=True;App=EntityFramework"
Adding user
Opened connection at 9/11/2019 9:44:08 AM +03:00
Started transaction at 9/11/2019 9:44:08 AM +03:00
INSERT [dbo].[CUsers]([UserId], [Deleted], [TraceVersion], [UTimeStamp], [NTUser], [FName], [LName], [Active])
VALUES (@0, @1, @2, @3, @4, @5, @6, @7)
SELECT [Id]
FROM [dbo].[CUsers]
WHERE @@ROWCOUNT > 0 AND [Id] = scope_identity()
-- @0: '1' (Type = Int32)
-- @1: 'False' (Type = Boolean)
-- @2: '1' (Type = Int32)
-- @3: '9/11/2019 9:44:08 AM' (Type = DateTime2)
-- @4: 'gmeyer' (Type = AnsiString, Size = 50)
-- @5: 'George' (Type = AnsiString, Size = 20)
-- @6: 'Meyer' (Type = AnsiString, Size = 50)
-- @7: 'True' (Type = Boolean)
-- Executing at 9/11/2019 9:44:09 AM +03:00
-- Completed in 45 ms with result: SqlDataReader
Committed transaction at 9/11/2019 9:44:09 AM +03:00
Closed connection at 9/11/2019 9:44:09 AM +03:00
user.Id=3
ChangeTracker.Entries().ToList().Count=1
Opened connection at 9/11/2019 9:44:09 AM +03:00
Started transaction at 9/11/2019 9:44:09 AM +03:00
UPDATE [dbo].[CUsers]
SET [LName] = @0
WHERE (([Id] = @1) AND ([TraceVersion] = @2))
-- @0: 'Meyers' (Type = AnsiString, Size = 50)
-- @1: '3' (Type = Int32)
-- @2: '1' (Type = Int32)
-- Executing at 9/11/2019 9:44:09 AM +03:00
-- Completed in 138 ms with result: 1
Committed transaction at 9/11/2019 9:44:09 AM +03:00
Closed connection at 9/11/2019 9:44:09 AM +03:00
Opened connection at 9/11/2019 9:44:09 AM +03:00
SELECT
[Extent1].[Id] AS [Id],
[Extent1].[UserId] AS [UserId],
[Extent1].[Deleted] AS [Deleted],
[Extent1].[TraceVersion] AS [TraceVersion],
[Extent1].[UTimeStamp] AS [UTimeStamp],
[Extent1].[NTUser] AS [NTUser],
[Extent1].[FName] AS [FName],
[Extent1].[LName] AS [LName],
[Extent1].[Active] AS [Active]
FROM [dbo].[CUsers] AS [Extent1]
WHERE [Extent1].[Id] = 3
-- Executing at 9/11/2019 9:44:10 AM +03:00
-- Completed in 14 ms with result: SqlDataReader
Closed connection at 9/11/2019 9:44:10 AM +03:00
user.TraceVersion=2
ChangeTracker.Entries().ToList().Count=1
Opened connection at 9/11/2019 9:44:10 AM +03:00
Started transaction at 9/11/2019 9:44:10 AM +03:00
UPDATE [dbo].[CUsers]
SET [Deleted] = @0
WHERE (([Id] = @1) AND ([TraceVersion] = @2))
-- @0: 'True' (Type = Boolean)
-- @1: '3' (Type = Int32)
-- @2: '2' (Type = Int32)
-- Executing at 9/11/2019 9:44:10 AM +03:00
-- Completed in 15 ms with result: 1
Committed transaction at 9/11/2019 9:44:10 AM +03:00
Closed connection at 9/11/2019 9:44:10 AM +03:00
context.Entry(user).State=Detached
ChangeTracker.Entries().ToList().Count=0
IAudited
接口 IAudited 由所有实体实现。它定义了所有实体都具有 Deleted、TraceVersion 和 UTimeStamp 列。
interface IAudited
{
bool Deleted { get; set; }
int TraceVersion { get; set; }
DateTime UTimeStamp { get; set; }
}
Model1.edmx
实体框架模型 Model1.edmx 是通过 Add new item/ADO.NET Entity Data Model/EF Designer from database 并选择数据库 DB1 和表 CUsers 创建的。在 TraceVersion 列的属性中，ConcurrencyMode 设置为 Fixed。
当表很多时，使用 XML (Text) Editor 打开 Model1.edmx 并搜索会更容易
<Property Name="TraceVersion" Type="Int32" Nullable="false" />
并替换为
<Property Name="TraceVersion" Type="Int32" Nullable="false" ConcurrencyMode="Fixed" />
将 ConcurrencyMode 设置为 Fixed 的作用是，所有更新语句都在 WHERE 子句中添加了 TraceVersion，例如
UPDATE [dbo].[CUsers]
SET [LName] = @0
WHERE (([Id] = @1) AND ([TraceVersion] = @2))
乐观并发就是这样实现的。
从 Model1.edmx 生成模板文件 Model1.Context.tt，并从该文件生成 C# 代码文件 Model1.Context.cs。同样从 Model1.edmx 生成模板文件 Model1.tt，用于为每个实体生成 C# 代码文件，在本例中为 CUser.cs。为了使此模型与给定数据库一起工作，需要进行以下修改。
Model1.Context.tt
此模板用于创建 Context 类。
- 添加了以下
using语句using System.Configuration; using System.Linq;
- 实体框架从 app.config 获取连接字符串。原始构造函数如下所示
public <#=code.Escape(container)#>() : base("name=<#=container.Name#>")然而，连接字符串通常是从其他模块检索的，它可能被编码并必须被解码，因此创建了函数GetConnectionString。此函数可用于检索连接字符串。在这种情况下，它也从 app.config 读取。构造函数调用函数GetConnectionString。private static string GetConnectionString() { return ConfigurationManager.ConnectionStrings["<#=code.Escape(container)#>"].ConnectionString; } public <#=code.Escape(container)#>() : base(GetConnectionString()) - 方法
SaveChanges被重写。public override int SaveChanges() { ... int rowCount = base.SaveChanges(); ... return rowCount; } - 模型应确保所有新实体都具有
Deleted=false、TraceVersion=1和UTimestamp=current date and time。以下代码执行此操作var entriesAdded = ChangeTracker.Entries().Where( e => e.State == EntityState.Added).ToList(); foreach (var item in entriesAdded) { if (item.Entity is IAudited entity) { entity.Deleted = false; entity.TraceVersion = 1; entity.UTimeStamp = DateTime.Now; } } - 模型应重新加载更新实体的
TraceVersion和UTimeStamp字段。不幸的是，只能重新加载整个实体。未来的改进应该是只重新加载TraceVersion和UTimeStamp字段。var entriesModified = ChangeTracker.Entries().Where( e => e.State == EntityState.Modified).ToList(); int rowCount = base.SaveChanges(); if (rowCount > 0) { entriesModified.ForEach(e=>e.Reload()); } - 模型应将已删除的实体更改为已更新的实体，并将
Deleted列设置为1。保存后，这些实体应分离。这归功于文章 Entity Framework Core: Soft Delete，该文章描述了如何实现软删除。这里它就像一个软删除，但记录从主表移动到审计表。var entriesDeleted = ChangeTracker.Entries().Where( e => e.State == EntityState.Deleted).ToList(); foreach (var item in entriesDeleted) { if (item.Entity is IAudited entity) { // Set the entity to unchanged // (if we mark the whole entity as Modified, // every field gets sent to Db as an update) item.State = EntityState.Unchanged; // Only update the Deleted flag - only this will get sent to the Db entity.Deleted = true; } } int rowCount = base.SaveChanges(); if (rowCount > 0) { foreach (var item in entriesDeleted) { if (item.Entity is IAudited entity) { item.State = EntityState.Detached; } } }
Model1.tt
此模板用于创建实体类，在本例中为 CUser.cs。唯一的修改是使每个 Entity 类实现接口 IAudited。类定义的原始模板是
public string EntityClassOpening(EntityType entity)
{
return string.Format(
CultureInfo.InvariantCulture,
"{0} {1}partial class {2}{3}",
Accessibility.ForType(entity),
_code.SpaceAfter(_code.AbstractOption(entity)),
_code.Escape(entity),
_code.StringBefore(" : ", _typeMapper.GetTypeName(entity.BaseType)));
}
它生成以下 Entity 类
public partial class CUser
entity.BaseType 似乎总是 null，因此生成的类不继承自某个基类型。模板更改为
public string EntityClassOpening(EntityType entity)
{
return string.Format(
CultureInfo.InvariantCulture,
"{0} {1}partial class {2} : IAudited",
Accessibility.ForType(entity),
_code.SpaceAfter(_code.AbstractOption(entity)),
_code.Escape(entity));
}
它生成以下 Entity 类
public partial class CUser : IAudited
摘要
本项目演示了可以创建一个 Entity Framework Model，它
- 确保所有新（添加）实体都获得
Deleted=false、TraceVersion=1和UTimeStamp=当前日期和时间。 - 重新加载所有更新的实体，以便实体获得由触发器提供的新
TraceVersion和UTimeStamp。 - 将所有删除更改为更新，并将
Deleted=1列，并在保存后分离这些实体。
历史
- 表
CUsers的模型，更新重新加载整个实体。 - 使用
TraceVersion列实现乐观并发。