Call SP from EF using the same transaction SaveChanges

Question

Does anyone know how to call StoredProc using the same transaction of the objectContext SaveChanges method (EntityFramework 5)?

The goal is to apply object changes and invoke a stored Proc that does some magic in the database, but if something goes wrong (either with SaveChanges or with SP), no changes will be made at all.

Answer 1

Steps:

• Create context
• get connection out of context
• Create a transaction (TransactionScope)
• Open the connection (the connection will close with the external transaction created in 3. and will not allow the connection to be closed by context)
• Save SaveChanges ()
• Running stored procedure
• Commit transaction
• Close connection

Some code (MyContext obtained 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(); } } }