using(TransactionScope ts = new TransactionScope())
{
...
... write your EF code and your TSQL code (sqlconenction + sqlcommand + ...)
...
ts.complete();
}
TransactionScope works fine but in many cases the transaction is escalated to a distributed transaction so you need to setup the MSDTC and you lose in performance.
Using NET 4.0 there is a simple and very powerful solution: ObjectContext.ExecuteStoreCommand(string)
The method sends the command directly to the underlying database. It uses the same connection of the ObjectContext. But... it's not transactional with SaveChanges. :(
Solution: a couple of command to explicitely open connection and manage a global transaction.
var mydb = new MyEFDB();
mydb.Connection.Open();
var tran = mydb.Connection.BeginTransaction();
...
// (now you can mix you EF code with your TSQL code)
...
mydb.ExecuteStoreCommand("DELETE FROM MyTable WHERE ...");
...
foreach(var c in mydb.Customers)
c.Name=....
...
mydb.SaveChanges();
tran.Commit();
mydb.Connection.Close();
Steps:
- open the db connection
- begina the transaction
- do your TSQL and EF
- save EF changes
- commit transaction
- close connection
