6

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 :

enter image description here

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 :

enter image description here

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 :

enter image description here

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

Community
  • 1
  • 1
  • This is not a duplicate. I can actually reuse the same connection. The problem is that it keeps opening / closing itself between the queries. –  May 14 '13 at 17:59
  • 1
    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). – Gert Arnold May 14 '13 at 18:07
  • It works ! But only if, as you suggested, I explicitly open the connection before using the context. Do you know why ? –  May 14 '13 at 18:15
  • Not sure why, probably it's a feature. I remembered having read about it somewhere. – Gert Arnold May 14 '13 at 18:20
  • @GertArnold : I invite you to post your comment as an answer and I will mark it as the answer. –  May 14 '13 at 18:21
  • 1
    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. – Servy May 14 '13 at 18:47

2 Answers2

4

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.

Gert Arnold
  • 93,904
  • 24
  • 179
  • 256
  • Thanks a lot ! It works perfectly by explicitly opening the connection before using the context. I edited my question to show the results. –  May 14 '13 at 18:54
1

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();
    }
}
youzer
  • 403
  • 4
  • 10