现在的位置: 首页 > 综合 > 正文

Using transaction with Entity Framework

2011年12月07日 ⁄ 综合 ⁄ 共 10644字 ⁄ 字号 小 中 大 ⁄ 评论关闭

在使用EF来处理业务逻辑时，一个必不可少的feature就是事务（Transaction），如何在使用EF时使用事务，在MSDN上的这篇文章有详细的说明：How to: Manage Transactions in the Entity Framework。由于有些描述理解起来担心有歧义，所以对一些基本的场景我做了如下检查。

 

一，数据库准备

数据库1：EFTransactionDemo (表：User, Thread)

CREATE TABLE [dbo].[User](
    [ID] [int] NOT NULL,
    [Name] [varchar](20) NULL,
    [Email] [varchar](200) NULL,
PRIMARY KEY CLUSTERED 
(
    [ID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
CREATE TABLE [dbo].[Thread](
    [ID] [int] NOT NULL,
    [Title] [varchar](100) NULL,
    [Content] [varchar](500) NULL,
    [Author] [int] NULL,
    [CreatedDate] [datetime] NULL,
PRIMARY KEY CLUSTERED 
(
    [ID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

ALTER TABLE [dbo].[Thread]  WITH CHECK ADD  CONSTRAINT [FK_Thread_User] FOREIGN KEY([Author])
REFERENCES [dbo].[User] ([ID])
GO

ALTER TABLE [dbo].[Thread] CHECK CONSTRAINT [FK_Thread_User]
GO

 

数据库2：EFTransactionDemo2 (表：BO, BOM)

CREATE TABLE [dbo].[BO](
    [ID] [int] NOT NULL,
    [Code] [varchar](10) NULL,
    [BillDate] [datetime] NULL,
PRIMARY KEY CLUSTERED 
(
    [ID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
CREATE TABLE [dbo].[BOM](
    [ID] [int] NOT NULL,
    [BOID] [int] NULL,
    [MaterialCode] [int] NULL,
    [Num] [int] NULL,
PRIMARY KEY CLUSTERED 
(
    [ID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

ALTER TABLE [dbo].[BOM]  WITH CHECK ADD  CONSTRAINT [FK_BOM_BO] FOREIGN KEY([BOID])
REFERENCES [dbo].[BO] ([ID])
GO

ALTER TABLE [dbo].[BOM] CHECK CONSTRAINT [FK_BOM_BO]
GO

 

之所以增加外键，是想通过外键约束来模拟插入失败的场景。

 

创建VS工程

创建一个Console Application: EFTransactionDemo1。

创建Library Project: DemoDataModel11, DemoDataModel12, DemoDataModel21。前两个对应EFTransactionDemo数据库，后一个是用于EFTransactionDemo2数据库的。

 

场景一：Object Context中SaveChanges是用一个事务提交的

using (EFTransactionDemoEntities context = new EFTransactionDemoEntities())
{
            User user = new User { ID = 1, Email = "bright.zhang@abc.com", Name = 

"Bright Zhang" };
            context.AddToUsers(user);
                        
            Thread thread = new Thread { ID = 1, Author = 2, Title = "how to do this", 
Content = "dfdfd ", CreatedDate = DateTime.Now };
            context.Threads.AddObject(thread);
            context.SaveChanges();
}

通过提交一个不存在的Author的Thread来模拟插入失败，从而验证了在一个Object Context中，一次SaveChanges是默认使用一个事务来提交的。

 

场景二：一个objectcontext内的多次SaveChanges是在不同事务内提交的

using (EFTransactionDemoEntities context = new EFTransactionDemoEntities())
{
          User user = new User { ID = 1, Email = "bright.zhang@abc.com", Name = "Bright 

Zhang" };
          context.AddToUsers(user);
          context.SaveChanges();

          Thread thread = new Thread { ID = 1, Author = 2, Title = "how to do this", 
Content = "dfdfd ", CreatedDate = DateTime.Now };
          context.Threads.AddObject(thread);
          context.SaveChanges();
}

 

场景三：对两个相同的Object Context使用TransactionScope来实现EF的事务

using (TransactionScope scope = new TransactionScope())
{
          using (EFTransactionDemoEntities context = new EFTransactionDemoEntities())
          {
                 User user = new User { ID = 1, Email = "bright.zhang@abc.com", Name = 
"Bright Zhang" };
                 context.AddToUsers(user);
                 context.SaveChanges
(System.Data.Objects.SaveOptions.DetectChangesBeforeSave);
           }

           using (EFTransactionDemoEntities context = new EFTransactionDemoEntities())
           {
                 Thread thread = new Thread { ID = 1, Author = 2, Title = "how to do this", 
Content = "dfdfd ", CreatedDate = DateTime.Now };
                 context.Threads.AddObject(thread);
                 context.SaveChanges(System.Data.Objects.SaveOptions.DetectChangesBeforeSave);
           }

           scope.Complete();
}

注意，这里使用多个Object Context进行提交，可以通过TransactionScope来确保他们在同一个事务中完成。注意一点，在每个Object Context提交修改时提供保存选项：context.SaveChanges(System.Data.Objects.SaveOptions.DetectChangesBeforeSave);，这样就可以了。据我所知，TransactionScope的使用会用到MSDTC。如果Object Context连接到相同的数据库，那么TransactionScope将直接使用SqlTransaction / OleDbTransaction提交，如果连接到不同的数据库，那么连接到两个数据库的事务将会被promote到DTC进行协调。在上面的这个例子中，同一个Object Context，在停止MSDTC之后仍然可以正常工作。那么连到同一个数据库的不同Object Context呢？（这里就用到了DemoDataModel21了）

 

场景四：对连接到同一数据库的两个不同Object Context使用TransactionScope来实现EF事务

using (TransactionScope scope = new TransactionScope())
{
          using (DemoDataModel11.EFTransactionDemoEntities11 context = new DemoDataModel11.EFTransactionDemoEntities11())
          {
              DemoDataModel11.User user = new DemoDataModel11.User { ID = 1, Email = "bright.zhang@abc.com", Name = "Bright Zhang" };
              context.AddToUsers(user);
              context.SaveChanges(System.Data.Objects.SaveOptions.DetectChangesBeforeSave);
          }

          using (DemoDataModel12.EFTransactionDemoEntities12 context = new DemoDataModel12.EFTransactionDemoEntities12())
          {
              DemoDataModel12.Thread thread = new DemoDataModel12.Thread { ID = 1, Author = 2, Title = "how to do this", Content = "dfdfd ", CreatedDate = DateTime.Now };
              context.AddToThreads(thread);
              context.SaveChanges(System.Data.Objects.SaveOptions.DetectChangesBeforeSave);
          }

          scope.Complete();
}

 通过上面的代码确认，连接到同一数据库的不同Object Context不需要被上升到MSDTC进行事务协调即可实现事务。那么连接到不同数据库的不同Object Context呢？先停掉MSDTC: net stop MSDTC。

 

场景五：对连接到不同数据库的两个不同Object Context使用TransactionScope来实现EF事务

using (TransactionScope scope = new TransactionScope())
{
          using (DemoDataModel11.EFTransactionDemoEntities11 context = new DemoDataModel11.EFTransactionDemoEntities11())
          {
              DemoDataModel11.User user = new DemoDataModel11.User { ID = 1, Email = "bright.zhang@abc.com", Name = "Bright Zhang" };
              context.AddToUsers(user);
              context.SaveChanges(System.Data.Objects.SaveOptions.DetectChangesBeforeSave);
          }

          using (DemoDataModel12.EFTransactionDemoEntities12 context = new DemoDataModel12.EFTransactionDemoEntities12())
          {
              DemoDataModel12.Thread thread = new DemoDataModel12.Thread { ID = 1, Author = 1, Title = "how to do this", Content = "dfdfd ", CreatedDate = DateTime.Now };
              context.AddToThreads(thread);
              context.SaveChanges(System.Data.Objects.SaveOptions.DetectChangesBeforeSave);
          }

          using (DemoDataModel21.EFTransactionDemo2Entities context = new DemoDataModel21.EFTransactionDemo2Entities())
          {
              DemoDataModel21.BO bo = new DemoDataModel21.BO { ID = 1, BillDate = DateTime.Now, Code = "001" };
              context.AddToBOes(bo);
              DemoDataModel21.BOM bom = new DemoDataModel21.BOM { ID = 1, BOID = 2, MaterialCode = 1001, Num = 10 };
              context.AddToBOMs(bom);
              context.SaveChanges(SaveOptions.DetectChangesBeforeSave);
          }

          scope.Complete();
}

通过在提交BO/BOM的时候，利用外键约束模拟插入失败的例子，发现报异常：

MSDTC on server 'XXX' is unavailable.

从这句话已经说明，在同一个TransactionScope下的不同Object Context访问不同数据库，会将各自的事务上升至MSDTC进行协调。

然后启用MSDTC，再执行，发现事务已经能够正常工作了。

 

场景六：EF和ADO.NET/Enterprise Library混用

如果把EF和Enterprise Library混用，那么TransactionScope又是如何实现事务的呢？

using (TransactionScope scope = new TransactionScope())
{
    using (DemoDataModel11.EFTransactionDemoEntities11 context = new DemoDataModel11.EFTransactionDemoEntities11())
    {
        DemoDataModel11.User user = new DemoDataModel11.User { ID = 1, Email = "bright.zhang@abc.com", Name = "Bright Zhang" };
        context.AddToUsers(user);
        context.SaveChanges(System.Data.Objects.SaveOptions.DetectChangesBeforeSave);
    }

    // insert a record using ado.net
    using (SqlConnection connection = new SqlConnection("Database=EFTransactionDemo;Server=.;Integrated Security=SSPI;"))
    {
        connection.Open();

        SqlCommand command = new SqlCommand("insert into [User] select 100, 'admin', 'admin@test.com'", connection);
        command.ExecuteNonQuery();
    }

    using (DemoDataModel12.EFTransactionDemoEntities12 context = new DemoDataModel12.EFTransactionDemoEntities12())
    {
        DemoDataModel12.Thread thread = new DemoDataModel12.Thread { ID = 1, Author = 100, Title = "how to do this", Content = "dfdfd ", CreatedDate = DateTime.Now };
        context.AddToThreads(thread);
        context.SaveChanges(System.Data.Objects.SaveOptions.DetectChangesBeforeSave);
    }

    scope.Complete();
}

 根据上面的实例代码，可以发现，对于混用的情况，当在TransactionScope中，如果有EF和ADO.NET混用，那么其事务需要升级到MSDTC进行协调。

 

场景七：ObjectContext.ExecuteStoreCommand/ExecuteStoreQuery/ExecuteFunction避免事务被升级到MSDTC

对于在EF中需要处理其他SQL语句，或者通过其他SQL语句实现特殊逻辑时，可以使用EF提供的一些途径。尤其在使用TransactionScope时非常必要。

using (TransactionScope scope = new TransactionScope())
{
    using (DemoDataModel11.EFTransactionDemoEntities11 context = new DemoDataModel11.EFTransactionDemoEntities11())
    {
        DemoDataModel11.User user = new DemoDataModel11.User { ID = 1, Email = "bright.zhang@abc.com", Name = "Bright Zhang" };
        context.AddToUsers(user);
        context.SaveChanges(System.Data.Objects.SaveOptions.DetectChangesBeforeSave);
    }

    using (DemoDataModel11.EFTransactionDemoEntities11 context = new DemoDataModel11.EFTransactionDemoEntities11())
    {
        context.ExecuteStoreCommand(@"insert dbo.[User] values(100, 'admin', 'admin@test.com')");
    }

    using (DemoDataModel12.EFTransactionDemoEntities12 context = new DemoDataModel12.EFTransactionDemoEntities12())
    {
        DemoDataModel12.Thread thread = new DemoDataModel12.Thread { ID = 1, Author = 101, Title = "how to do this", Content = "dfdfd ", CreatedDate = DateTime.Now };
        context.AddToThreads(thread);
        context.SaveChanges(System.Data.Objects.SaveOptions.DetectChangesBeforeSave);
    }

    scope.Complete();
}

通过EF提供的渠道，可以直接利用Object Context的连接，这也是TransactionScope可以发挥作用的主要原因。

 

我尝试用ObjectContext的Connection直接创建DbCommand,然后执行一个操作，结果提示错误。代码如下：

using (DemoDataModel11.EFTransactionDemoEntities11 context = new DemoDataModel11.EFTransactionDemoEntities11())
{
    context.Connection.Open();
    DbCommand command0 = context.Connection.CreateCommand();
    command0.CommandType = System.Data.CommandType.Text;
    command0.CommandText = @"insert dbo.[User] values(100, 'admin', 'admin@test.com')";
    command0.ExecuteNonQuery();
}

 

错误如下：

The query syntax is not valid. Near identifier 'dbo', line 1, column 8.
at System.Data.Common.EntitySql.CqlParser.yyerror(String s)
at System.Data.Common.EntitySql.CqlParser.yyparse()
at System.Data.Common.EntitySql.CqlParser.internalParseEntryPoint()
at System.Data.Common.EntitySql.CqlParser.Parse(String query)
at System.Data.Common.EntitySql.CqlQuery.Parse(String commandText, ParserOpti
ons parserOptions)
at System.Data.Common.EntitySql.CqlQuery.CompileCommon[TResult](String comman
dText, Perspective perspective, ParserOptions parserOptions, Func`3 compilationF
unction)
at System.Data.Common.EntitySql.CqlQuery.Compile(String commandText, Perspect
ive perspective, ParserOptions parserOptions, IEnumerable`1 parameters)
at System.Data.EntityClient.EntityCommand.MakeCommandTree()
at System.Data.EntityClient.EntityCommand.CreateCommandDefinition()
at System.Data.EntityClient.EntityCommand.TryGetEntityCommandDefinitionFromQu
eryCache(EntityCommandDefinition& entityCommandDefinition)
at System.Data.EntityClient.EntityCommand.GetCommandDefinition()
at System.Data.EntityClient.EntityCommand.InnerPrepare()
at System.Data.EntityClient.EntityCommand.Prepare()
at System.Data.EntityClient.EntityCommand.ExecuteReader(CommandBehavior behav
ior)
at System.Data.EntityClient.EntityCommand.ExecuteScalar[T_Result](Func`2 resu
ltSelector)
at System.Data.EntityClient.EntityCommand.ExecuteNonQuery()
at EFTransactionDemo1.Program.Main(String[] args) in D:\temp\vs\EFTransaction
Demo1\EFTransactionDemo1\Program.cs:line 96

 

这个问题，没有找到原因。

 

总结

Entity Framework可以通过TransactionScope来使用事务，其中，多个Object Context操作同一个数据库时，不需要将事务上升到MSDTC，而直接使用SqlTransaction/OleDbTransaction。而如果访问多个数据库，那么MSDTC则需要启动，用于不同Object Context之间的事务协调。

尤其是多个Object Context访问同一个数据库时，使用TransactionScope不需要使用MSDTC这一点，对于一些小型的应用系统的架构有着明显的好处。因为MSDTC是一个昂贵的方案，不管是性能问题，还是对组件服务的依赖，都可能成为架构决策时的重要的考量点。

此外，当需要通过其他SQL脚本/存储过程来实现特殊的业务逻辑或操作时，尽量使用EF提供的渠道：ObjectContext.ExecuteStoreCommand, ObjectContext.ExecuteStoreQuery, ObjectContext.ExecuteFunction。如果需要实现事务，且不想通过MSDTC进行事务协调，那么只能使用这些渠道。反之，多个操作的事务会被提交到MSDTC进行协调处理。

 

 

返回
【上篇】
【下篇】

作者:

抱歉!评论已关闭.

返回首页

Copyright © 2013-2018 学步园  保留所有权利.
软文销售 QQ客服：2265327166 点击这里给我发消息（其他合作也可洽谈）
必威体育
必威电竞