How to mix entity framework and tsql commands

Entity Framewok is a powerful library. But sometimes you need to mix Entity Framework activities and standard TSQL command on the same connection and under the same transaction. The easiest way to do that is using a TransactionScope area:

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