0x80131904 dotnet data_error ai_generated partial

Microsoft.Data.SqlClient.SqlException (0x80131904): Execution Timeout Expired. The timeout period elapsed prior to completion of the operation or the server is not responding. The statement has been terminated.

ID: dotnet/ef-core-sql-timeout-large-data

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

Version Compatibility

VersionStatusIntroducedDeprecatedNotes
6.0 active — — —
7.0 active — — —
8.0 active — — —
9.0 active — — —

Root Cause

SQL query execution exceeds the configured command timeout, typically due to large data volumes, missing indexes, or inefficient LINQ queries generated by Entity Framework Core.

generic

中文

SQL 查询执行超过配置的命令超时时间，通常由大数据量、缺少索引或 Entity Framework Core 生成的低效 LINQ 查询引起。

Official Documentation

https://learn.microsoft.com/en-us/sql/relational-databases/errors-events/mssqlserver-2-database-engine-error

Workarounds

  1. 85% success Optimize the LINQ query by adding .Where() filters early and using .Select() to project only needed columns. Example: var data = context.Orders.Where(o => o.Date > DateTime.UtcNow.AddDays(-30)).Select(o => new { o.Id, o.Total }).ToList(); 
    Optimize the LINQ query by adding .Where() filters early and using .Select() to project only needed columns. Example: var data = context.Orders.Where(o => o.Date > DateTime.UtcNow.AddDays(-30)).Select(o => new { o.Id, o.Total }).ToList();
  2. 90% success Add database indexes on columns used in WHERE, JOIN, and ORDER BY clauses. Use SQL Server Management Studio or EF Core migrations: CREATE INDEX IX_Orders_Date ON Orders (Date); 
    Add database indexes on columns used in WHERE, JOIN, and ORDER BY clauses. Use SQL Server Management Studio or EF Core migrations: CREATE INDEX IX_Orders_Date ON Orders (Date);
  3. 75% success Increase CommandTimeout temporarily for the specific operation using: context.Database.SetCommandTimeout(120); or in connection string: "Command Timeout=120;" 
    Increase CommandTimeout temporarily for the specific operation using: context.Database.SetCommandTimeout(120); or in connection string: "Command Timeout=120;"

中文步骤

  1. 通过早期添加 .Where() 过滤器和使用 .Select() 投影仅需要的列来优化 LINQ 查询。示例：var data = context.Orders.Where(o => o.Date > DateTime.UtcNow.AddDays(-30)).Select(o => new { o.Id, o.Total }).ToList();
  2. 在 WHERE、JOIN 和 ORDER BY 子句中使用的列上添加数据库索引。使用 SQL Server Management Studio 或 EF Core 迁移：CREATE INDEX IX_Orders_Date ON Orders (Date);
  3. 临时增加特定操作的 CommandTimeout：context.Database.SetCommandTimeout(120); 或在连接字符串中："Command Timeout=120;"

Dead Ends

Common approaches that don't work:

  1. Increasing CommandTimeout to a very high value (e.g., 300 seconds) without optimizing the query 70% fail

    Temporary fix that masks the underlying performance issue; query may still time out under heavy load or with larger datasets.

  2. Adding .AsNoTracking() to all queries without understanding if tracking is needed 50% fail

    AsNoTracking improves performance for read-only queries but does not fix inefficient joins or missing indexes.

  3. Splitting the query into multiple smaller queries without using .Include() properly 65% fail

    Multiple round trips can increase total execution time and cause N+1 query problems, worsening performance.