Calling SP from EF using same transaction of SaveChanges

entity-framework, objectcontext, stored-procedures

Solution

Steps:

- Create the context

- get the connection from the context

- Create the transaction (TransactionScope)

- Open the connection (will enlist the connection into the ambient transaction created in 3. and will prevent from closing the connection by the context)

- Do SaveChanges()

- Execute your stored procedure

- Commit the transaction

- Close the connection

Some code (MyContext is derived from DbContext):

using (var ctx = new MyContext())
{
    using (var trx = new TransactionScope())
    {
        var connection = ((IObjectContextAdapter)ctx).ObjectContext.Connection;
        try
        {
            ctx.Entities.Add(new MyEntity() { Number = 123 });
            ctx.SaveChanges();

            ctx.Database.ExecuteSqlCommand("INSERT INTO MyEntities VALUES(300)");
            trx.Complete();
        }
        finally
        {
            connection.Close();
        }
    }
}

Problem

Does someone knows how to call a StoredProc using the same transaction of an objectContext SaveChanges method (EntityFramework 5)? The goal is to apply the objects changes and call a stored Proc that does some "magic" on the DB, but, if something goes wrong (either with the SaveChanges or with the SP execution) no changes would be committed at all.

Original source