LINQ-to-EF 效率：一个警示故事

引言

我喜欢使用 SQL Server 的 SQL Profiler 来监视 Entity Framework 和 Linq 生成的 TSQL。 它们生成动态过程，这些过程通常非常出色，有时令人困惑。

如果您在 Linq 查询中观察到不太令人满意的性能，或者怀疑网络上的不必要负载，通常有必要揭开向导的幕布，看看幕后发生了什么。 最近对我们的 Linq 例程之一的更改在效率方面带来了巨大的好处。

背景

我们的几个应用程序为最终用户提供了一个组合框，用户可以从中选择一个客户。 我喜欢用区域设置信息来补充客户的名称，以便用户可以选择具有多个位置的客户的正确帐户。 存储该数据的数据库表看起来像提供的图表。

Using the Code

每个客户帐户可以有多个地址类型——“账单”、“发货”等。它们中的每一个都有一个相关的国家/地区记录。 我希望我的组合框下拉列表中的每一行看起来像 <客户名称> <国家/地区> <地址>。

我们将实体数据传输到强类型数据集。 用于执行此操作的原始代码位于 Customer DataTable 分部类中，类似于

// Establish the ObjectContext (which opens the data connection)
using (Base.Data.DLTIEntities edmDLTI = new Base.Data.DLTIEntities)
{
       // Define the query (this does NOT cause a trip to the database yet)
       IQueryable<Base.Data.Customer> qryCust = from c in edmDLTI.Customers
              where c.ActiveInd || c.CustomerName == "Unknown Customer"
              orderby c.CustomerName, c.CustomerNumber
              select c
              ;

    // The ToArray() call will cause the query to execute a single round trip to the database.
       foreach (Durcon.Base.Data.Customer entCust in qryProjCust.ToArray<Base.Data.Customer>())
       {
              // Assign DataTable field values and add the DataRow to the DataTable
              CustomerRow dtrCustomer = NewCustomerRow();
              dtrCustomer.ActiveInd = entCust.ActiveInd;
              dtrCustomer.CustomerID = entCust.CustomerID;
              dtrCustomer.CustomerText = entCust.CustomerName.Trim() + " (" 
                     + entCust.CustomerNumber.Trim() + ")"
                     ;

              if (entCust.CustomerAddresses.Count > 0)
              {
                     // Get the Customer’s "first" address as dictated by its Address Type (which is usually the "Billing" address)
                     Base.Data.Address entAddr = entCust.CustomerAddresses.OrderBy(ca => ca.Address.AddressTypes.AddressTypeDesc).FirstOrDefault<Base.Data.CustomerAddress>().Address;
                     CustomerText += " " + entAddr.City.Trim() + (entAddr.State.Trim().Length == 0 ? "" : ", ")
                           + entAddr.State.Trim()
                           + (entAddr.Country.CountryDesc.Trim().Length == 0 || entAddr.Country.CountryDesc.Trim() == "US" ? "" : ", " + entAddr.Country.CountryDesc.Trim())
                           ;
              }

              AddCustomerRow(dtrCustomer);
       }

// Close the database connection.
}

通过 EF 的延迟加载数据轻松导航关系的能力非常诱人。 就像其他许多超能力一样，它必须谨慎使用，并且只能为了更大的利益而使用。

上面每个以粗体显示的段每次遇到时都会调用与数据库服务器的往返。 我们会期望第一个，因为我们需要一组客户来迭代。 但是，在迭代循环中，每个以粗体显示的段都需要每个客户记录进行数据库往返。

对于 1,000 个客户记录的列表，这导致了与数据库的 4,000 多次往返。 更糟糕的是，Linq-to-Entities 通常每次往返执行多个 TSQL 事务——一个用于建立参数及其值，另一个用于执行 SQL 语句等等。 将其乘以每个客户组合框。 然后乘以每个最终用户。 上述方法需要整整 6 秒才能填充每个列表，并且需要大量的数据库事务。

请不要告诉我们的网络管理员。

微软的专家经常吹捧 Linq-to-EF 设计的数据库服务器执行和 .NET 代码执行之间的隔离。 我对它的新细微差别是，两者应该尽可能地分隔开。

我们对更有效方法的研究产生了以下代码

首先创建一个占位符类

public class ListCustomer
{
       private bool booActiveInd = false;
       private long lngCustomerID = 0;
       private string strCustomerText = "";
       private string strCustAddr = "";

       public bool ActiveInd { get { return booActiveInd; } set { booActiveInd = value; } }
       public long CustomerID { get { return lngCustomerID; } set { lngCustomerID = value; } }
       public string CustomerText { get { return strCustomerText; } set { strCustomerText = value; } }
       public string CustAddr { get { return strCustAddr; } set { strCustAddr = value; } }
}

然后，在初始查询设计中增加一些复杂性，并且仍然在强类型的 Customer DataTable 镜像分部类中进行编码

// Establish the ObjectContext (which opens the data connection)
using (Base.Data.DLTIEntities edmDLTI = new Base.Data.DLTIEntities)
{
       // Create an all-encompassing query statement that will ensure a single database round-trip 
       IQueryable<ListCustomer> qryQuoteCust = from c in edmDLTI.Customers
              where c.ActiveInd || c.CustomerName == "Unknown Customer"
              orderby c.CustomerName, c.CustomerNumber
              // row-append single instances of the place-holder class
              select new ListCustomer
              {
                     ActiveInd = c.ActiveInd,
                     CustomerID = c.CustomerID,
                     CustomerText = c.CustomerName.Trim() + " (" + c.CustomerNumber.Trim() + ") ",
                     CustAddr = (  from ca in edmDLTI.CustomerAddresses
                                         // Despite what we’ve read, these all resolve to TSQL INNER JOINs.  Watch SQL Profiler.
                                         join a in edmDLTI.Addresses on ca.AddressID equals a.AddressID
                                         join at in edmDLTI.AddressTypes on a.AddressTypeID equals at.AddressTypeID
                                          join co in edmDLTI.Countries on a.CountryID equals co.CountryID
                                   where ca.CustomerID == c.CustomerID
                                  orderby at.AddressTypeDesc
                                  select a.City.Trim()
                                         // These c# "if" clauses create interesting CASE clauses in TSQL.  Watch SQL Profiler.
                                         + (a.State.Trim().Length == 0 ? "" : ", ") + a.State.Trim()
                                          + (co.CountryDesc.Trim().Length == 0 || co.CountryCode.Trim() == "US" ? "" : ", " + co.CountryDesc.Trim())
                     // Grab only the first record that respects the Address Type "orderby" sort.  Emulates a TSQL "TOP 1" clause.  Also one of the few ways to emulate a true TSQL "LEFT OUTER JOIN".
                     ).FirstOrDefault(),
              }
              ;
       // A single TSQL statement and database round-trip is executed here.
foreach (ListCustomer licCust in qryQuoteCust)
{
    CustomerRow dtrCust = NewCustomerRow();
    dtrCust.ActiveInd = licCust.ActiveInd;
    dtrCust.CustomerID = licCust.CustomerID;
    dtrCust.CustomerText = licCust.CustomerText + licCust.CustAddr;
    AddCustomerRow(dtrCust);
}
// Close the database connection.
}

关注点

所有数据收集任务都由初始查询执行，并且没有后续访问数据库以获取其他关系数据 - 没有延迟加载。 哦，方法上的改变会带来什么……

执行时间：不到一秒。 数据库往返：1

请告诉我们的网络管理员。

结果并不总是证明手段是正当的。