.NET5+Dapper异步查MSSQL偶发SqlDataReader关闭致NextResult报错

基础运行环境 #

• 技术栈：.NET 5.0 + Dapper 2.0.78，基于Microsoft.Data.SqlClient实现异步多语句查询
• 数据库配置：已安装最新补丁的SQL Server 2016，16GB内存，Intel Xeon E5-2630 v4 2.2GHz 6个虚拟处理器
• 前置排查结论：所有SQL语句在SSMS中执行无异常，表的索引、主键配置合理，单语句执行耗时极短；故障为偶发复现，无稳定触发路径，初步怀疑SQL连接被意外关闭。

第一阶段报错（原始代码版本） #

报错栈信息：

System.InvalidOperationException: Invalid attempt to call NextResult when reader is closed.    
at Microsoft.Data.SqlClient.SqlDataReader.TryNextResult(Boolean& more)
at Microsoft.Data.SqlClient.SqlDataReader.NextResult()    
at Dapper.SqlMapper.GridReader.NextResult() in /_/Dapper/SqlMapper.GridReader.cs:line 414    
at Dapper.SqlMapper.GridReader.ReadDeferred[T](Int32 index, Func`2 deserializer, Type effectiveType)+System.IDisposable.Dispose()    
at Dapper.SqlMapper.GridReader.ReadDeferred[T](Int32 index, Func`2 deserializer, Type effectiveType)+MoveNext() in /_/Dapper/SqlMapper.GridReader.cs:line 384    
at System.Collections.Generic.List`1..ctor(IEnumerable`1 collection)
at System.Linq.Enumerable.ToList[TSource](IEnumerable`1 source)

对应原始简化业务代码：

private async Task<RecipeListModel> GetRecipesByIngredientAsync(int id)
{
    string sql = @"SELECT Id,Title FROM dbo.[Ingredients] WHERE Id = @id;
                   SELECT ID,Title FROM dbo.[Recipes] WHERE IngredientId = @Id;"
    RecipeListModel model = new() { };
    using (SqlConnection conn = new("my connection here"))
    {
        var data = await conn.QueryMultipleAsync(sql, new { id }).ConfigureAwait(false);
        model = new RecipeListModel
        {
            Ingredient = data.ReadAsync<Ingredient>().Result.SingleOrDefault(),
            Recipes = data.ReadAsync<Recipe>().Result.ToList()
        };
    }
    return model;
}

// 关联实体定义
public class Ingredient
{
    public int Id { get; set; }
    public string Title { get; set; }
}

public class Recipe
{
    public int Id { get; set; }
    public string Title { get; set; }
}

public class RecipeListModel
{
    public IEnumerable<Recipe> Recipes { get; set; }
    public Ingredient Ingredient { get; set; }
}

第二阶段报错（异步优化代码版本） #

将原始代码中同步阻塞的.Result调用替换为全异步读逻辑后，故障仍偶发出现，新报错栈为：

System.InvalidOperationException: Invalid attempt to call NextResultAsync when reader is closed.    
at Microsoft.Data.Common.ADP.ExceptionWithStackTrace(Exception e)
--- End of stack trace from previous location ---    
at Dapper.SqlMapper.GridReader.NextResultAsync() in /_/Dapper/SqlMapper.GridReader.Async.cs:line 157    
at Dapper.SqlMapper.GridReader.ReadBufferedAsync[T](Int32 index, Func`2 deserializer) in /_/Dapper/SqlMapper.GridReader.Async.cs:line 241    
at myproject.Controllers.HomeController.GetRecipesByIngredientAsync(Int32 p, Int32 id, String title, String q) in C:\Web\myproject\myproject-net\Controllers\HomeController.cs:line 502    
at myproject.Controllers.HomeController.Ingredient(String title, Int32 id, String q, Int32 page) in C:\Web\myproject\myproject-net\Controllers\HomeController.cs:line 125

优化后代码逻辑：

• 控制器层：配置食材详情页路由，基于SEO规则校验URL与食材名称是否匹配，根据食谱总数计算分页参数
• 数据层：根据搜索词是否为空，动态拼接全文检索关联逻辑，通过CTE实现总行数统计、食谱标签拼接、分页偏移查询，一次查询返回“食材基础信息”“食谱分页列表”两个结果集
• 读写逻辑：用using包裹SqlConnection与GridReader对象，全程通过await调用ReadSingleOrDefaultAsync、ReadAsync读取结果，无同步阻塞调用

已梳理的潜在故障原因 #

• 原始代码的同步阻塞隐患：在请求线程上直接对异步方法返回的Task取.Result，高峰期易触发线程池饥饿，导致请求上下文提前回收，关联的数据库连接被dispose
• 网络层静默断连：硬编码连接串未配置合理的连接超时、重试参数，应用与数据库之间的防火墙、负载均衡设备若存在空闲连接超时设置，会静默掐断TCP连接，后续读取结果时会发现reader已被标记为关闭
• 动态SQL执行异常：当搜索词包含特殊字符、全文索引检索超时，可能导致SQL Server提前终止查询，未完整返回两个预期结果集，GridReader尝试切换结果集时会触发reader已关闭的异常
• 请求超时提前触发：若Kestrel的请求超时配置阈值过低，会主动取消请求关联的CancellationToken；因代码未将CancellationToken传递到数据查询层，无法感知请求已被终止，仍在尝试读取已被回收的reader

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