这个SqlTransaction已经完成了它不再可用。实体框架代码第一 [英] This SqlTransaction has completed; it is no longer usable. Entity Framework Code First

问题描述

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using System.ComponentModel.DataAnnotations;
using System.Data.Entity;

namespace SqlEFTester {
    class Program {
        static void Main(string[] args) {

            ConnectionManager connectionManager = new ConnectionManager();
            SqlConnection conn = connectionManager.Connection;
            conn.Open();

            //BEGIN TRAN
            SqlTransaction tran = conn.BeginTransaction();

            //Will do some legacy work using SqlTransaction, so can not use TransactionScope here.

            MyContext context = new MyContext(conn);
            List<Inventory> list = context.Inventories.Take(10).ToList();//Just get top 10 Inventories

            //COMIT TRAN
            tran.Commit();

            Console.WriteLine("Done...");
            Console.ReadLine();
        }
    }

class ConnectionManager {
    public SqlConnection Connection {
        get {
            return new SqlConnection("Data Source=MYSERVER;Initial Catalog=MYDATABASE;Integrated Security=SSPI;");
        }
    }
}

[Table("Inventory")]
class Inventory {
    [Key]
    public int InventoryId { get; set; }
}

class MyContext : DbContext {
    public EmutContext(SqlConnection conn)
        : base(conn, false) {
        //I have to close it, otherwise it will say "EntityConnection can only be constructed with a closed DbConnection."
            base.Database.Connection.Close();
    }

    public DbSet<Inventory> Inventories { get; set; }
}

我想要做的是在传统ADO.NET中执行EF代码 SqlTransaction 。

What I am trying to do is execute EF code inside traditional ADO.NET SqlTransaction.

我打开一个连接并将相同的连接传递给EF以重新使用连接。根据设计，我必须关闭连接，因为它的抱怨

I open a connection and pass the same connection to EF to reuse the connection. By design, I have to close the connection since its complaining

EntityConnection只能用闭合的DbConnection构造。

EntityConnection can only be constructed with a closed DbConnection.

如果我运行上面的代码，我得到了

If I run above code I got

事务已经完成。

Transaction has completed.

我无法提交。注意：由于我们使用的旧代码框架，我不能使用 TransactionScope 。

I can not commit. NOTE: I can not use TransactionScope because of legacy code framework we use.

推荐答案

嗯，我最好使用TransactionScope，但作为一种解决方法，你可以尝试使用反射（也许有更好的方法，不确定）。请注意，我正在将封闭的SqlConnection传递给上下文，然后打开EntityConnection（这将打开SqlConnection）

Well, I would better use TransactionScope, but as a workaround you can try to use reflection (maybe there are a better way, not sure). Please note that I am passing closed SqlConnection to context, then open EntityConnection (this will open SqlConnection)

        //create connection & context
        SqlConnection sqlConnection = new SqlConnection("your connection");
        YourDbContext context = new YourDbContext(sqlConnection, false);

        var entityConnection = (EntityConnection)((IObjectContextAdapter)context).ObjectContext.Connection;
        try
        {
            //open entity connection - will open store connection
            entityConnection.Open();

            var entityTransaction = entityConnection.BeginTransaction();

            //get sql transaction via reflection
            var sqlTransaction = (SqlTransaction)entityTransaction.GetType()
                                                      .InvokeMember("StoreTransaction",
                                                                    BindingFlags.FlattenHierarchy | BindingFlags.NonPublic | BindingFlags.InvokeMethod
                                                                    | BindingFlags.Instance | BindingFlags.GetProperty | BindingFlags.NonPublic, null, entityTransaction, new object[0]);



            //here you got both sql transaction & sql connectont
            var cmd = sqlConnection.CreateCommand();
            cmd.Transaction = sqlTransaction;
            cmd.CommandText = "your sql statement";
            cmd.ExecuteNonQuery();

            //do your context work - will be in entity transaction

            //invoke save changes
            context.SaveChanges();

            entityTransaction.Commit();
        }
        finally
        {
            entityConnection.Close();
        }

这篇关于这个SqlTransaction已经完成了它不再可用。实体框架代码第一的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！