How do you assign a transaction with EF4 CodeFirst

Question

I am attempting to perform functional tests against a live database, to make sure my linq queries are translating into SQL correctly. I want to do this by using transactions, so that one functional test does not affect another.

The problem is, I cannot find any way in the API to utilize transactions correctly. I can retrieve a new DbTransaction via MyDbContext.Database.Connection.BeginTransaction(), however, I cannot find anyway to actually use this transaction.

All documentation I can find about the BeginTransaction() call, is you assign the transaction object to the SqlCommand via a cmd.Transaction call for the command you are performing actions with. However, with EF4 CTP5, there is no way to access the SqlCommand used for the queries.

Thus, I can't figure out how to use the transaction I have begun with BeginTransaction().

I do not want to use TransactionScope objects, mostly because Sql CE 4 does not support them, and I would prefer to use a local database and not go across a network. Sql CE 4 DOES support transactions retrieved via BeginTransaction(), I just can't figure out how with code first.

Does anyone have any idea how to do this?

---

Edit: After some emails with Microsoft, it seems that TransactionScope() calls are meant to be the primary way to use transactions with EF4. To get TransactionScope to work with Sql CE you have to explicitely open the database connection prior to starting the transaction, for example

    [TestMethod]
    public void My_SqlCeScenario ()
    {
        using (var context = new MySQLCeModelContext()) //ß derived from DbContext
        {
            ObjectContext objctx = ((IObjectContextAdapter)context).ObjectContext;
            objctx.Connection.Open(); //ß Open your connection explicitly
            using (TransactionScope tx = new TransactionScope())
            {

                var product = new Product() { Name = "Vegemite" };
                context.Products.Add(product);
                context.SaveChanges();
            }
            objctx.Connection.Close(); //ß close it when done!
        }
    }

Answer 1

This is cheesy, but may be your only option here:

((EntityConnection)myObjectContext.Connection).StoreConnection.BeginTransaction(...

Answer 2

Maybe this article on MSDN might help Managing Connections & Transactions then scroll down to the heading Transactions and Entity Framework. That's where I would start.

EDIT: Here's a better one: How To: Manage Transactions in the Entity Framework