许多查询和同一连接的太多打开/关闭 [英] Many queries and too much opening / closing of the same connection

问题描述

我必须一个接一个地重新计算大量实体的值.

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

在此过程中，所有自跟踪实体都在同一ObjectContext中更改.对于每个需要处理的实体，必须从数据库中获取少量数据.这样会导致很多相同的SQL查询，但是使用不同的参数.

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.

我正在使用 Solutions Design的ORM Profiler 软件来分析发送到数据库的查询.

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 :

如您所见，它会继续打开和关闭相同的数据库连接.

现在，看看单个打开/查询/关闭连接的时间:

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 建议我在使用上下文之前先明确打开连接.然后，我修改了代码，使其看起来像这样:

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 :

我现在很好奇为什么在使用上下文之前需要打开连接?

推荐答案

可以使用现有连接创建上下文.很难找到有关它的文档，但是如果在上下文使用它之前显式打开了连接，它将一直保持打开状态，直到显式关闭或处置它为止.我用EF5 ObjectContext(Linqpad代码)进行了测试:

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();
    }
}

输出为Open，Open.当未打开连接时，输出为Closed，Closed.

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

这篇关于许多查询和同一连接的太多打开/关闭的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！