IT:AD:EF:HowTo:Keep the Same Connection
Summary
EF handles the lifespan of its own connections, pulling them from a pool. Every operation you get will get a different scope.
Which is fine for performance, and ensuring that as the DbConnections from the pool are reallocated as needed, there is no left overs passed around – but it's not useful when you need to set some form of Db context variable (eg: SET FORCEPLAN) then do a query.
Process
Normally, I let the repository invoke a UnitOfWorkService, which in turn gets the current UnitOfWork/DbContext from a a factory, that in turn builds a new DbContext for the current HttpContext.
In such cases, the DbContext gets a new DbConnection from the pool as needed.
But if you use the other overload of the DbContext, you can take ownership of what DbConnection the DbContext uses.
PseudoCode:
using (var dbConnection = AppDbConnectionFactory.Create()){
using (var dbContext = new AppDbContext(dbConnection)){
...
use repository to issue SQL Statements to SET FORCEPLAN
...
do query...
...etc.
}
}
Note that the entities retrieve from this DbContext are not in the same context as the thread's dbContext. If you need them there, you have to disconnect and reconnect the entities…
To create the AppDbConnecitonFactory do something like: * In Infrastructure, Nuget a reference to XAct.Data.Db * Make a App.Infrastructure.Data.AppDbCOnnectionFactory, with a Create() method. * Use the XAct.Data.DbHelpers class within the method.
public class AppDbConnectionFactory {
public DbConnection CreateConnection(){
return XAct.Data.Db.CreateConnection("AppDbContext");
}
public DbContext CreateContext(){
return new AppDbContext(CreateConnection());
}
}
- Use that method from where needed:
using (var dbContext = AppDbConnectionFactory.CreateContext(){
..go...
}
It's all a bit crappy – but you get the point.