score:0

Accepted answer

I think I figured out a solution that works for my situation. Rather than wrapping SqlConnection and overriding Open() to change databases, I'm passing the DBContext a new SqlConnection and subscribing to the connection's StateChanged event. When the state changes, I check to see if the connection has just been opened. If so, I call SqlConnection.ChangeDatabase() to point it to the correct database. I tested this solution and it seems to work - I see only one connection pool for all the databases rather than one pool for each db that has been accessed.

I realize this isn't the ideal solution in an ideal application, but for how this application is structured I think it should make a decent improvement for relatively little cost.

score:2

I don't think that's going to work off a single shared connection.

LINQ to SQL works best with Unit of Work type connections - create your connection, do your atomically grouped work and close the connection as quickly as possible and reopen for the next task. If you do that then passing in a connection string (or using custom constructor that only passes a tablename) is pretty straight forward.

If factoring your application is a problem, you could use a getter to manipulate the cached DataContext 'instance' and instead create a new instance each time you request it instead of using the cached/shared instance and inject the connection string in the Getter.

But - I'm pretty sure this will not help with your pooling issue though. The SQL Server driver caches connections based on different connection string values - since the values are still changing you're right back to having lots of connections active in the connection string cache, which likely will result in lots of cache misses and therefore slow connections.

score:0

I think, that the best way is making UnitOfWork pattern with Repository pattern to work with Entity Framework. Entity Framework has FirstAsync, FirstOrDefaultAsync, this helped me to fix the same bug.

https://msdn.microsoft.com/en-us/data/jj819165.aspx


Related Articles