Many requests and opening / closing too much of the same connection

Question

I need to recalculate the values ​​in a large collection of objects one by one.

During this process, all self-monitoring objects are changed within the same ObjectContext. For each object that needs to be processed, small amounts of data must be extracted from the database. This results in a lot of the same SQL query, but using different parameters.

I use Design ORM Profiler Solutions to profile queries sent to the database.

The queries themselves seem useful to me. They are short and do not require much time to complete.

However, I am confused by the way the profiler shows me how requests are processed:

As you can see, it continues to open and close the same database connection.

Now take a look at the time for one Open / Query / Close connection:

It seems like opening and closing a database connection is time consuming.

After reading this answer , I changed my code, so now it looks like this:

using (var connection = new EntityConnection(ConfigurationManager.ConnectionStrings["MyEntities"].ConnectionString)) { using (var context = new MyEntities(connection)) { // ... 

Now I can see that it is still using the same connection (which is good), but the connection continues to close and open between requests.

Geert Arnold suggested that I openly open the connection before using context. Then I changed my code to look like this:

 using (var connection = new EntityConnection(ConfigurationManager.ConnectionStrings["MyEntities"].ConnectionString)) { connection.Open(); using (var context = new MyEntities(connection)) { // ... 

Now it works! Each request is sent to the same database connection:

I am now curious that why do I need to open a connection before using context?

Answer 1

You can create a context with an existing connection. It is difficult to find documentation about this, but if the connection is openly open before the context uses it, it will remain open until it is explicitly closed or deleted. I tested this with an EF5 ObjectContext (Linqpad code):

 using (var conn = new EntityConnection(connectionString)) { conn.Open(); using (var db = new InventoryContext(conn)) { db.Products.ToList(); conn.State.Dump(); db.SaveChanges(); conn.State.Dump(); } } 

Output signal Open , Open . When the connection does not open, the output signal is Closed , Closed .

Answer 2

Another solution might be to open a connection when building a DbContext :

 public partial class QueryModel : DbContext { public QueryModel(string connectionName):base(connectionName) { this.Database.Connection.Open(); } }