目录
介绍
本文提供了将Entity Framework 6与现有SQL Server表一起使用的示例。这些表具有更新触发器，可将当前版本的记录复制到相应的审计表，并使用新的TraceVersion和UTimeStamp更新记录。
背景
数据库中的所有表都有4个额外的列用于审计目的：
- 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 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 |
- 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 |
- 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=0IAudited
接口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是有效果的，即所有更新语句都将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=<# =container.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 CUserentity.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：
- 确保所有新的（添加的）实体获得Deleted=false，TraceVersion=1并且UTimeStamp=当前日期和时间。
- 重新加载所有更新的实体，使实体获得新的TraceVersion和UTimeStamp被触发给出。
- 将所有删除更改为更新列Deleted=1，保存后分离这些实体。
扫一扫
221
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
