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

Question

I have to recalculate values on a large collection of entities, one after the other.

During that process, all the self-tracking-entities are altered within the same ObjectContext. For each entity that needs to be processed, small amounts of data has to be fetched from the database. That results in a lot of the same SQL query but using different parameters.

I am using Solutions Design's ORM Profiler software to profile the queries that are sent to the database.

The queries themselves seems okay to me. They are short and don't take much time to execute.

However, I am confused about how the profiler shows me how the queries are actually processed :

As you can see, it keeps opening and closing the same database connection.

Now, take a look at the times for a single Open/Query/Close connection :

It looks like opening and closing a database connection wastes time.

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))
    {
        // ...

I can now see that it is still using the same connection (which is good), but, the connection still keeps closing and opening itself between the queries.

Gert Arnold suggested that I explicitly open the connection before using the context. I then modified my code so it looks like this :

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

Now it works ! Every query is sent to the same database connection :

I am now curious as why do I need to open the connection before using the context ?

Ormond

This is not a duplicate. I can actually reuse the same connection. The problem is that it keeps opening / closing itself between the queries. – Ormond

Does it also close when you open the connection prior to creatiing the context? (By the way I think that your code is not entirely correct: new EntityConnection twice). – Andvari

It works ! But only if, as you suggested, I explicitly open the connection before using the context. Do you know why ? – Ormond

Not sure why, probably it's a feature. I remembered having read about it somewhere. – Andvari

@GertArnold : I invite you to post your comment as an answer and I will mark it as the answer. – Ormond

What you really want to be doing is allowing the context to leverage connection pooling. This is such a common requirement that virtually any provider will have already implemented it; you just need to enable it. I'm not familiar enough with EF to tell you where this setting is specified though. – Calamint

Answer 1

It is possible to create a context with an existing connection. It's hard to find documentation about it, but if the connection is opened explicitly before the context uses it, it will stay open until it is explicitly closed or disposed. 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();
    }
}

The output is Open, Open. When the connection is not opened the output is Closed, Closed.

Andvari

Thanks a lot ! It works perfectly by explicitly opening the connection before using the context. I edited my question to show the results. – Ormond

Answer 2

Another solution could be to open the connection when the DbContext is constructed:

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

Floss