0x80131904 dotnet data_error ai_generated true

System.InvalidOperationException: An error occurred while executing the query. The query has been canceled. See InnerException for details. -> Microsoft.Data.SqlClient.SqlException (0x80131904): Execution Timeout Expired. The timeout period elapsed prior to completion of the operation or the server is not responding.

ID: dotnet/ef-core-query-split-query-timeout

Also available as: JSON · Markdown · 中文
85%Fix Rate
85%Confidence
1Evidence
2023-08-05First Seen

Version Compatibility

VersionStatusIntroducedDeprecatedNotes
EF Core 5.0 active — — —
EF Core 6.0 active — — —
EF Core 7.0 active — — —
EF Core 8.0 active — — —
SQL Server 2016+ active — — —

Root Cause

Entity Framework Core's split query mode (enabled via AsSplitQuery()) generates multiple SQL queries that execute sequentially, and the total time exceeds the default 30-second command timeout, especially with large datasets or multiple includes.

generic

中文

Entity Framework Core的分割查询模式（通过AsSplitQuery()启用）生成按顺序执行的多个SQL查询，总时间超过默认的30秒命令超时，尤其是在大型数据集或多个Include的情况下。

Official Documentation

https://learn.microsoft.com/en-us/ef/core/querying/single-split-queries

Workarounds

  1. 80% success Increase the command timeout specifically for the problematic query using .CommandTimeout(120) on the DbContext or query-level. 
    Increase the command timeout specifically for the problematic query using .CommandTimeout(120) on the DbContext or query-level.
  2. 90% success Optimize the query by reducing the number of Includes, using explicit loading, or batching data retrieval with multiple queries. 
    Optimize the query by reducing the number of Includes, using explicit loading, or batching data retrieval with multiple queries.

中文步骤

  1. Increase the command timeout specifically for the problematic query using .CommandTimeout(120) on the DbContext or query-level.
  2. Optimize the query by reducing the number of Includes, using explicit loading, or batching data retrieval with multiple queries.

Dead Ends

Common approaches that don't work:

  1. Removing AsSplitQuery() and relying on single query with joins, which may cause cartesian explosion 50% fail

    Single queries with many Includes can result in huge result sets and memory pressure, leading to different performance issues.

  2. Setting CommandTimeout to a very high value (e.g., 5 minutes) globally in DbContext options 20% fail

    This masks the underlying performance issue and may cause other queries to hang indefinitely.