Opening a DbContext connection in an existing SqlConnection
I am wondering if opening an Entity Framework connection DbContext
in an existing ADO.NET SqlConnection
should be discouraged assuming they both use the same connection string i.e. work with the exact same database?
For example:
using (TransactionScope scope = new TransactionScope(TransactionScopeOption.Required, new System.TimeSpan(0, 30, 0)))
{
using (SqlConnection connection = new SqlConnection(connectionString))
{
connection.Open();
DoStuffWithEF(connectionString);
...
}
}
void DoStuffWithEF(string connectionString)
{
using (var context = new MyCodeFirstDbContext(connectionString))
{
// basic LINQ queries ...
}
}
- Is the connection reusable in both cases, given that the connection string refers to the same SQL Server 2012 database in both cases?
- Is there a danger that such an operation would require MSDTC?
source to share
A connection string is not a connection. You never pass the actual connection to the context, so it must create a new one. As a consequence, the transaction will grow into a distributed transaction to span both connections.
You need to pass the actual connection object to the context using the appropriate constructor . In this case, there will be only one local transaction and there is no need to escalate it into a distributed transaction.
In EF6 +, you can just pass a connection object . Change your method as follows:
void DoStuffWithEF(SqlConnection connection)
{
using(var context=new MyCodeFirstDbContext(connection,false)
{
// ...
}
}
In previous versions, you could not get through an open connection which required some nasty gymnastics as described here
source to share