使用ToListAsync与CancellationToken时的数据库连接异常及执行疑问

问题背景 #

在ASP.NET Core服务类中，DbContext通过依赖注入提供。为优化耗时数据库查询的异步处理，使用ToListAsync并传入请求的CancellationToken，代码如下：

await _dbContext.ViewMappings.Where(e => e.Level == Level && e.Code.ToString() == Code).Select(c => new
{
    Id = c.Id,
    Label = c.Label,
    parentId = c.ParentId,
    parentLabel = c.ParentLabel
}).ToListAsync(token);

// 检查取消请求
token.ThrowIfCancellationRequested();

错误现象 #

当用户离开页面触发请求取消时，出现数据库连接错误，错误栈如下：

An error occurred using the connection to database 'XXX' on server 'YYY'
An exception occurred while iterating over the results of a query for context type
  at Microsoft.EntityFrameworkCore.Diagnostics.Internal.RelationalConnectionDiagnosticsLogger.ConnectionErrorAsync(IRelationalConnection connection, Exception exception, DateTimeOffset startTime, TimeSpan duration, Boolean logErrorAsDebug, CancellationToken cancellationToken)
   at Microsoft.EntityFrameworkCore.Storage.RelationalConnection.OpenInternalAsync(Boolean errorsExpected, CancellationToken cancellationToken)
   at Microsoft.EntityFrameworkCore.Storage.RelationalConnection.OpenAsync(CancellationToken cancellationToken, Boolean errorsExpected)
   at Microsoft.EntityFrameworkCore.Storage.RelationalCommand.ExecuteReaderAsync(RelationalCommandParameterObject parameterObject, CancellationToken cancellationToken)
   at Microsoft.EntityFrameworkCore.Query.Internal.SingleQueryingEnumerable`1.AsyncEnumerator.InitializeReaderAsync(AsyncEnumerator enumerator, CancellationToken cancellationToken)
   at Microsoft.EntityFrameworkCore.SqlServer.Storage.Internal.SqlServerExecutionStrategy.ExecuteAsync[TState,TResult](TState state, Func`4 operation, Func`4 verifySucceeded, CancellationToken cancellationToken)
   at Microsoft.EntityFrameworkCore.Query.Internal.SingleQueryingEnumerable`1.AsyncEnumerator.MoveNextAsync()    at Microsoft.Extensions.Logging.Logger.ThrowLoggingError(List`1 exceptions)
   at Microsoft.Extensions.Logging.Logger.Log[TState](LogLevel logLevel, EventId eventId, TState state, Exception exception, Func`3 formatter)
   at Microsoft.Extensions.Logging.LoggerMessage.<>c__DisplayClass12_0`2.<Define>g__Log|0(ILogger logger, T1 arg1, T2 arg2, Exception exception)
   at Microsoft.EntityFrameworkCore.Diagnostics.Internal.RelationalConnectionDiagnosticsLogger.ConnectionErrorAsync(IRelationalConnection connection, Exception exception, DateTimeOffset startTime, TimeSpan duration, Boolean logErrorAsDebug, CancellationToken cancellationToken)
   at Microsoft.EntityFrameworkCore.Storage.RelationalConnection.OpenInternalAsync(Boolean errorsExpected, CancellationToken cancellationToken)
   at Microsoft.EntityFrameworkCore.Storage.RelationalConnection.OpenAsync(CancellationToken cancellationToken, Boolean errorsExpected)
   at Microsoft.EntityFrameworkCore.Storage.RelationalCommand.ExecuteReaderAsync(RelationalCommandParameterObject parameterObject, CancellationToken cancellationToken)
   at Microsoft.EntityFrameworkCore.Query.Internal.SingleQueryingEnumerable`1.AsyncEnumerator.InitializeReaderAsync(AsyncEnumerator enumerator, CancellationToken cancellationToken)
   at Microsoft.EntityFrameworkCore.SqlServer.Storage.Internal.SqlServerExecutionStrategy.ExecuteAsync[TState,TResult](TState state, Func`4 operation, Func`4 verifySucceeded, CancellationToken cancellationToken)
   at Microsoft.EntityFrameworkCore.Query.Internal.SingleQueryingEnumerable`1.AsyncEnumerator.MoveNextAsync()

其他场景下使用async/await结合CancellationToken均正常，仅ToListAsync出现该错误。查看.NET Core中ToListAsync的实现：

public static async Task<List<TSource>> ToListAsync<TSource>(
            this IQueryable<TSource> source,
            CancellationToken cancellationToken = default)
{
    var list = new List<TSource>();
    await foreach (var element in source.AsAsyncEnumerable().WithCancellation(cancellationToken))
    {
        list.Add(element);
    }

    return list;
}

后续尝试将代码包裹进try块，可捕获TaskCanceledException，但发现任务取消时，异步调用后的同步代码仍会执行：

try
{
var results = await _dbContext.ViewMappings.Where(e => e.Level == Level && e.Code.ToString() == Code).Select(c => new
            {
                Id = c.Id,
                Label = c.Label,
                parentId = c.ParentId,
                parentLabel = c.ParentLabel
            }).ToListAsync(token);

//check for cancel request
token.ThrowIfCancellationRequested();

  foreach(var result in results)
  {
     Debug.WriteLine("processing results");
  }
}

取消请求触发时，仍能看到"processing results"的输出。

问题根源 #

1. 数据库连接错误的原因 #

当取消令牌触发时，ToListAsync内部的异步枚举器正处于从数据库读取数据的阶段。此时取消令牌传递到数据库驱动层面，会强制中断数据库连接，EF Core会抛出底层的连接错误（而非标准的TaskCanceledException），这是因为连接中断的优先级高于取消异常的转换。

2. 同步代码仍执行的原因 #

• 若取消请求发生在ToListAsync执行完成之后，token.ThrowIfCancellationRequested()会抛出异常，但如果取消发生在foreach循环执行过程中，由于循环内未检查取消令牌，会继续执行完整个循环。
• 部分场景下，ToListAsync在取消令牌触发前已完成数据读取，此时await正常返回结果，后续代码会继续执行，直到遇到token.ThrowIfCancellationRequested()才会抛出异常，但如果取消是在循环开始后触发的，循环仍会完成。

解决方案 #

1. 统一处理取消相关的异常 #

将ToListAsync调用及后续处理逻辑包裹在try/catch中，同时捕获TaskCanceledException和数据库连接错误，并将后者判断为取消相关场景处理：

try
{
    var results = await _dbContext.ViewMappings
        .Where(e => e.Level == Level && e.Code.ToString() == Code)
        .Select(c => new
        {
            Id = c.Id,
            Label = c.Label,
            parentId = c.ParentId,
            parentLabel = c.ParentLabel
        })
        .ToListAsync(token);

    // 处理结果前再次检查取消
    token.ThrowIfCancellationRequested();

    foreach (var result in results)
    {
        // 循环内定期检查取消，确保及时响应
        token.ThrowIfCancellationRequested();
        Debug.WriteLine("processing results");
    }
}
catch (TaskCanceledException)
{
    // 处理取消逻辑，如记录日志
}
catch (DbException ex)
{
    // 根据数据库类型判断是否为取消导致的连接错误
    if (IsCancellationRelatedDbError(ex))
    {
        // 按取消场景处理
    }
    else
    {
        // 抛出其他数据库错误
        throw;
    }
}

以SQL Server为例，判断取消相关错误的辅助方法：

private bool IsCancellationRelatedDbError(DbException ex)
{
    if (ex is SqlException sqlEx)
    {
        // SQL Server中请求取消对应的错误码为1222
        return sqlEx.Number == 1222;
    }
    // 其他数据库类型可根据对应错误码调整
    return false;
}

2. 全流程检查取消令牌 #

• 在await之后立即检查取消令牌，避免无意义的结果处理。
• 在耗时的循环或业务逻辑中，定期调用token.ThrowIfCancellationRequested()，确保能及时响应取消请求。

3. 无需迁移异步调用至单独方法 #

不需要将ToListAsync调用移至单独方法，核心是正确处理异常及全流程检查取消令牌。

内容的提问来源于stack exchange，提问作者biostat