具有审计表的实体框架

目录

介绍

背景

使用代码

Main方法

运行程序

IAudited

Model1.edmx

总结

• 下载TestEntityFramework.zip - 20 KB

介绍

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

 

背景

数据库中的所有表都有4个额外的列用于审计目的：

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

SQL操作执行以下操作：

1. INSERT：在Insert上没有触发器，记录按原样插入表中。数据访问层可确保Deleted=0，TraceVersion=1，UTimeStamp=当前日期和时间。
2. UPDATE：有一个AFTER UPDATE触发器。如果
   • Deleted=0：将表中的当前记录插入审计表，然后更新当前记录，TraceVersion增加1，并将其UTimeStamp设置为当前日期和时间。
   • Deleted=1：与Deleted=0相同，但是额外的，更新的记录（Deleted=1）也会插入到审计表中，并从主表中删除。
3. DELETE：AFTER DELETE触发器禁止DELETE声明。删除记录必须通过将Deleted列更新为1。

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

1. INSERT CUsers(UserId,Deleted,TraceVersion,UTimeStamp,NTUser,FName,LName,Active) VALUES(1,0,1,GETDATE(),'gmeyer','George','Meyer',1) 一条记录插入主表：

表

ID

用户身份

删除

TraceVersion

UTimeStamp

NTUSER

FName参数

LName的

活性

CUsers

2

1

0

1

2019-09-10 11:08:23.340

gmeyer

George

Meyer

1

1. UPDATE CUsers SET LName='Meyers' WHERE Id=2当前记录（TraceVersion=1）将插入到Audit表中。更新的记录得到TraceVersion=2：

表

ID

用户身份

删除

TraceVersion

UTimeStamp

NTUSER

FName参数

LName的

活性

CUsers_Audit

2

1

0

1

2019-09-10 11:08:23.340

gmeyer

George

Meyer

1

CUsers

2

1

0

2

2019-09-10 11:17:03.640

gmeyer

George

Meyers

1

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

表

ID

用户身份

删除

TraceVersion

UTimeStamp

NTUSER

FName参数

LName的

活性

CUsers_Audit

2

1

0

1

2019-09-10 11:08:23.340

gmeyer

George

Meyer

1

CUsers_Audit

2

1

0

2

2019-09-10 11:17:03.640

gmeyer

George

Meyers

1

CUsers_Audit

2

1

0

3

2019-09-10 11:17:44.020

gmeyer

George

Meyers

1

没有记录CUsers。

用于创建表，触发器和插入管理用户的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。

使用代码

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

Main方法

Program.cs中的Main方法完全按照上述语句插入，更新和删除记录，但使用SQLEntity 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并进行搜索更容易

并替换为

设置ConcurrencyMode为 Fixed是有效果的，即所有更新语句都将TraceVersion添加到WHERE子句中，例如：

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类。

1、添加了以下using声明：

using System.Configuration;
using System.Linq;

2、实体框架从app.config获取连接字符串。原始构造函数如下所示：

public DB1Entities(): base("name=") {}

但是，连接字符串通常是从其他模块中检索的，它可能是编码的并且必须进行解码，因此创建了GetConnectionString函数。此函数可用于检索连接字符串。在本例中，此函数还会从app.config中读取连接字符串。构造函数调用GetConnectionString函数。

private static string GetConnectionString() {
    return ConfigurationManager.ConnectionStrings["DB1Entities"].ConnectionString;
}

public DB1Entities()
    : base(GetConnectionString())
{
}

3、重写方法SaveChanges。

public override int SaveChanges()
    {
        ...
        int rowCount = base.SaveChanges();
        ...
        return rowCount;
    }

4、该模型应该确保所有新的实体有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;
    }
}

5、该模型应重新加载更新的实体的字段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());
}

6、模型应将已删除的实体更改为列Deleted设置为1的更新实体。保存后，应分离这些实体。这就像软删除，但记录从主表移动到审计表。

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

public partial class CUser : IAudited

总结

该项目证明可以创建一个Entity Framework Model：

1. 确保所有新的（添加的）实体获得Deleted=false，TraceVersion=1并且UTimeStamp=当前日期和时间。
2. 重新加载所有更新的实体，使实体获得新的TraceVersion和UTimeStamp被触发给出。
3. 将所有删除更改为更新列Deleted=1，保存后分离这些实体。