entity-framework - 此 SqlTransaction 已完成；它不再可用。 Entity Framework 代码优先

Question

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 SqlTransaction 中执行 EF 代码。

我打开一个连接并将相同的连接传递给 EF 以重用该连接。按照设计，我必须关闭连接，因为它会提示

EntityConnection can only be constructed with a closed DbConnection.

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

Transaction has completed.

我不能 promise 。注意:由于我们使用遗留代码框架，我无法使用 TransactionScope。

Answer 1

好吧，我最好使用 TransactionScope，但作为一种解决方法，您可以尝试使用反射(也许有更好的方法，但不确定)。请注意，我将关闭的 SqlConnection 传递给上下文，然后打开 EntityConnection(这将打开 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();
        }