c# - LINQ-Expression 失败但 foreach 循环有效 Entity-Framework

Question

我的应用程序是一个 ASP.NET Core 1.0 Web API。

我的代码中有以下 LINQ 表达式:

int number = 0;
var orders = await this.DataRepo.Where(data => data.number == number).ToListAsync();

如果我尝试运行代码，它会失败并给我以下错误消息:

Microsoft.EntityFrameworkCore.Query.Internal.SqlServerQueryCompilationContextFactory:Error: An exception occurred in the database while iterating the results of a query. System.Data.SqlClient.SqlException: Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding. ---> System.ComponentModel.Win32Exception: Der Wartevorgang wurde abgebrochen --- End of inner exception stack trace --- at System.Data.SqlClient.SqlCommand.<>c.b__107_0(Task`1 result) at System.Threading.Tasks.ContinuationResultTaskFromResultTask`2.InnerInvoke() at System.Threading.Tasks.Task.Execute() --- End of stack trace from previous location where exception was thrown --- at System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task) at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task) at Microsoft.EntityFrameworkCore.Storage.Internal.RelationalCommand.d__20.MoveNext() --- End of stack trace from previous location where exception was thrown --- at System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task) at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task) at Microsoft.EntityFrameworkCore.Query.Internal.AsyncQueryingEnumerable.AsyncEnumerator.d__8.MoveNext() --- End of stack trace from previous location where exception was thrown --- at System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task) at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task) at Microsoft.EntityFrameworkCore.Query.Internal.AsyncLinqOperatorProvider.SelectAsyncEnumerable`2.SelectAsyncEnumerator.d__4.MoveNext() --- End of stack trace from previous location where exception was thrown --- at System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task) at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task) at Microsoft.EntityFrameworkCore.Query.Internal.AsyncLinqOperatorProvider.SelectAsyncEnumerable`2.SelectAsyncEnumerator.d__4.MoveNext() --- End of stack trace from previous location where exception was thrown --- at System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task) at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task) at Microsoft.EntityFrameworkCore.Query.Internal.AsyncLinqOperatorProvider.ExceptionInterceptor`1.EnumeratorExceptionInterceptor.d__5.MoveNext() ClientConnectionId:64923a0e-cf94-487c-be83-a43b719d8c45 Error Number:-2,State:0,Class:11

System.Data.SqlClient.SqlException: Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding. ---> System.ComponentModel.Win32Exception: Der Wartevorgang wurde abgebrochen --- End of inner exception stack trace --- at System.Data.SqlClient.SqlCommand.<>c.b__107_0(Task`1 result) at System.Threading.Tasks.ContinuationResultTaskFromResultTask`2.InnerInvoke() at System.Threading.Tasks.Task.Execute() --- End of stack trace from previous location where exception was thrown --- at System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task) at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task) at Microsoft.EntityFrameworkCore.Storage.Internal.RelationalCommand.d__20.MoveNext() --- End of stack trace from previous location where exception was thrown --- at System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task) at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task) at Microsoft.EntityFrameworkCore.Query.Internal.AsyncQueryingEnumerable.AsyncEnumerator.d__8.MoveNext() --- End of stack trace from previous location where exception was thrown --- at System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task) at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task) at Microsoft.EntityFrameworkCore.Query.Internal.AsyncLinqOperatorProvider.SelectAsyncEnumerable`2.SelectAsyncEnumerator.d__4.MoveNext() --- End of stack trace from previous location where exception was thrown --- at System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task) at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task) at Microsoft.EntityFrameworkCore.Query.Internal.AsyncLinqOperatorProvider.SelectAsyncEnumerable`2.SelectAsyncEnumerator.d__4.MoveNext() --- End of stack trace from previous location where exception was thrown --- at System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task) at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task) at Microsoft.EntityFrameworkCore.Query.Internal.AsyncLinqOperatorProvider.ExceptionInterceptor`1.EnumeratorExceptionInterceptor.d__5.MoveNext() ClientConnectionId:64923a0e-cf94-487c-be83-a43b719d8c45 Error Number:-2,State:0,Class:11 Ausnahme ausgelöst: "System.Data.SqlClient.SqlException" in System.Private.CoreLib.ni.dll

但是，如果我将 LINQ 表达式更改为以下循环，则一切正常:

var orders = new List<ExampleClass>();
int number = 0;

foreach (var data in DataRepo)
{
   if (data.number == number)
   {
      orders.Add(data);
   }
}

我没见过有同样问题的人..

有人知道为什么会这样吗？非常感谢

Answer 1

由于超时是由 SQL Client 报告的，我猜测这不是 Linq 表达式的问题，而是针对数据库执行已编译的 SQL 语句的问题。该异常给出了两个提示:

• 操作完成前超时时间已过
• 服务器没有响应

Microsoft.EntityFrameworkCore.Query.Internal.SqlServerQueryCompilationContextFactory:Error: An exception occurred in the database while iterating the results of a query. System.Data.SqlClient.SqlException: Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding.

关于你的第二个代码在超时时间内将整个表加载到内存中，我们可以排除第二个选项。

The default timeout of the SQL Connection is 15 seconds.这应该适合执行简单的查询，例如您的代码将生成的查询。当加载整个表比通过 where 子句中的单个参数过滤它更快时，很可能是索引问题:

• 缺少“数字”的索引
• 索引高度分散或其统计信息不是最新的
• 索引不合适，因为数千行的 'number' = 0
• 表的聚簇索引是碎片化的
• 其他原因

为了将问题集中在数据库中，我将提取 SQL 查询 - 通过在调试器中跟踪 SQL 字符串或使用 SQL Server Profiler - 在 SQL Server Management Studio 中执行它并分析执行计划以获取更多信息.

为了排除异步查询执行的副作用，我还会在默认的同步上下文中进行一些测试。