entity-framework - EF Core 5 升级 - 查询超时

Question

我们很晚才将一个成功的 .net Core 2.1 MVC 应用程序升级到 .net Core 5，除了一些令人困惑的 Microsoft.Data.SqlClient.SqlException 'Execution Timeout Expired' 过去在 EF Core 2.1 中运行良好的许多查询出现异常。

这是我们遇到问题的查询之一的示例

 var products = await _context.ShopProducts
                    .Include(p => p.Category)
                    .Include(p => p.Brand)
                    .Include(p => p.CreatedBy)
                    .Include(p => p.LastUpdatedBy)
                    .Include(p => p.Variants).ThenInclude(pv => pv.ProductVariantAttributes)
                    .Include(p => p.Variants).ThenInclude(pv => pv.CountryOfOrigin)
                    .Include(p => p.Page)
                    .Include(p => p.CountryOfOrigin)
                    .OrderBy(p => p.Name)
                    .Where(p =>
                        (string.IsNullOrEmpty(searchText)
                            || (
                                    p.Name.Contains(searchText)
                                    || p.Description.Contains(searchText)
                                    || p.Variants.Any(v => v.SKU.Contains(searchText))
                                    || p.Variants.Any(v => v.GTIN.Contains(searchText))
                                    || p.Brand.BrandName.Contains(searchText)
                                    || p.CountryOfOriginCode == searchText
                                    || p.Category.Breadcrumb.Contains(searchText)
                                )
                         )
                    ).ToPagedListAsync(page, pageSize);
            

还有我们遇到的异常(exception)情况。

Microsoft.Data.SqlClient.SqlException (0x80131904): Execution Timeout Expired.  The timeout period elapsed prior to completion of the operation or the server is not responding.
Execution Timeout Expired.  The timeout period elapsed prior to completion of the operation or the server is not responding.
 ---> System.ComponentModel.Win32Exception (258): The wait operation timed out.
   at Microsoft.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
   at Microsoft.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
   at Microsoft.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose)
   at Microsoft.Data.SqlClient.TdsParserStateObject.ThrowExceptionAndWarning(Boolean callerHasConnectionLock, Boolean asyncClose)
   at Microsoft.Data.SqlClient.TdsParserStateObject.ReadSniError(TdsParserStateObject stateObj, UInt32 error)
   at Microsoft.Data.SqlClient.TdsParserStateObject.ReadSniSyncOverAsync()
   at Microsoft.Data.SqlClient.TdsParserStateObject.TryReadNetworkPacket()
   at Microsoft.Data.SqlClient.TdsParserStateObject.TryPrepareBuffer()
   at Microsoft.Data.SqlClient.TdsParserStateObject.TryReadByteArray(Span`1 buff, Int32 len, Int32& totalRead)
   at Microsoft.Data.SqlClient.TdsParserStateObject.TryReadChar(Char& value)
   at Microsoft.Data.SqlClient.TdsParser.TryReadPlpUnicodeCharsChunk(Char[] buff, Int32 offst, Int32 len, TdsParserStateObject stateObj, Int32& charsRead)
   at Microsoft.Data.SqlClient.TdsParser.TryReadPlpUnicodeChars(Char[]& buff, Int32 offst, Int32 len, TdsParserStateObject stateObj, Int32& totalCharsRead)
   at Microsoft.Data.SqlClient.TdsParser.TryReadSqlStringValue(SqlBuffer value, Byte type, Int32 length, Encoding encoding, Boolean isPlp, TdsParserStateObject stateObj)
   at Microsoft.Data.SqlClient.TdsParser.TryReadSqlValue(SqlBuffer value, SqlMetaDataPriv md, Int32 length, TdsParserStateObject stateObj, SqlCommandColumnEncryptionSetting columnEncryptionOverride, String columnName, SqlCommand command)
   at Microsoft.Data.SqlClient.SqlDataReader.TryReadColumnInternal(Int32 i, Boolean readHeaderOnly)
   at Microsoft.Data.SqlClient.SqlDataReader.ReadColumnHeader(Int32 i)
   at Microsoft.Data.SqlClient.SqlDataReader.IsDBNull(Int32 i)
   at lambda_method1671(Closure , QueryContext , DbDataReader )
   at Microsoft.EntityFrameworkCore.Query.RelationalShapedQueryCompilingExpressionVisitor.ShaperProcessingExpressionVisitor.PopulateIncludeCollection[TIncludingEntity,TIncludedEntity](Int32 collectionId, QueryContext queryContext, DbDataReader dbDataReader, SingleQueryResultCoordinator resultCoordinator, Func`3 parentIdentifier, Func`3 outerIdentifier, Func`3 selfIdentifier, IReadOnlyList`1 parentIdentifierValueComparers, IReadOnlyList`1 outerIdentifierValueComparers, IReadOnlyList`1 selfIdentifierValueComparers, Func`5 innerShaper, INavigationBase inverseNavigation, Action`2 fixup, Boolean trackingQuery)
   at lambda_method1679(Closure , QueryContext , DbDataReader , ResultContext , SingleQueryResultCoordinator )
   at Microsoft.EntityFrameworkCore.Query.Internal.SingleQueryingEnumerable`1.Enumerator.MoveNext()

如果未指定 searchText 参数，此查询将完美运行，所以我认为它必须与索引/数据相关，但运行为查询生成的 SQL，有和没有 searchText 参数，直接在数据库上运行时立即执行，所以似乎排除了数据库的问题。

EF Core 5 是否难以将所有数据组装到对象实例中？我意识到我们从这个查询中返回了一个大对象树，总共 152 列，但由于 pageSize 变量只有 10 行。

当没有指定 searchText 时它不会超时，并且 EF Core 2.1 能够毫无问题地将它们放在一起，这似乎也不太有意义。

非常感谢任何有关调整查询方法的建议，或任何人从他们自己的 EF Core 2.1 升级到 3.1/5 的见解，或从异常堆栈跟踪中跳出的任何内容。

更新

很抱歉在 SO 上进行实时调试，但我发现查询中的 p.Description.Contains(searchText) 子句似乎导致超时。如果我将其注释掉，则查询运行成功。

Product.Description 数据是一个最多 1028 个字符的 HTML 字符串，平均长度为 350 个字符，同样，直接在 SQL 中查询完全没有问题。但它会以其他方式导致 EF 出现问题吗？

[DataType(DataType.Html)]
public string Description { get; set; }

Answer 1

考虑使用 Split Queries使用大量 Include 来提高查询的性能。