This SqlTransaction has completed; it is no longer

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; }
}

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

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 can only be constructed with a closed DbConnection.

If I run above code I got

Transaction has completed.

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

Answer 1

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();
        }