在.NET Core中使用原生SQL遇DataReader未关闭异常的解决方法

错误根源 #

触发异常的核心原因是：SqlQuery<string>返回的是延迟加载的查询结果，formatos并没有立即执行SQL读取数据。当你遍历它时，EF会打开DataReader并保持连接状态，但此时你又调用ExecuteSql复用同一个连接执行插入操作，导致连接上已有未关闭的DataReader，进而引发冲突。另外手动调用CloseConnection()属于错误操作——EF会自动管理连接生命周期，提前关闭会导致后续遍历formatos时直接报错。

正确实现方案 #

1. 立即加载结果到内存：对formatos调用.ToList()，一次性将所有数据读取到内存，自动释放DataReader和连接。
2. 移除手动关闭连接的代码：EF会根据操作需求自动打开/关闭连接，手动干预会破坏其生命周期管理逻辑。
3. 用参数化查询规避SQL注入：直接拼接字符串存在严重注入风险，必须通过EF的参数化方式传递变量。
4. 使用异步方法匹配async签名：方法标记为async，应使用EF提供的异步方法（如ToListAsync()、ExecuteSqlAsync()）保证异步流程一致性。

修正后的代码 #

public async Task<IActionResult> Refresh_Formatos()
{            
    using (var context = _context)
    {
        // 异步查询并立即加载结果到内存
        var padre = await context.Database.SqlQuery<int>(
            @"SELECT id
              FROM pds_etiquetas where nombre='FORMATOS' and tipo = 'Cat'")
            .ToListAsync();
        int padre_id = padre[0];
       
        // 异步查询并立即加载formatos到内存，释放DataReader
        var formatos = await context.Database.SqlQuery<string>(
            @"SELECT xdescripcion
              FROM vc00_formatos a
              left join pds_etiquetas c on a.xdescripcion=c.Nombre and @PadreId=c.PadreId and c.tipo = 'Val'
              where c.id is null
              order by xdescripcion",
            new SqlParameter("@PadreId", padre_id))
            .ToListAsync();

        // 遍历内存中的数据，执行参数化插入
        foreach (var formato in formatos)
        {
            var affectedRows = await context.Database.ExecuteSqlAsync(
                @"INSERT INTO [imp].[pds_etiquetas]
                       ([Nombre]
                       ,[Tipo]
                       ,[PadreId])
                 VALUES
                       (@Nombre
                       ,'Val'
                       ,@PadreId)",
                new SqlParameter("@Nombre", formato),
                new SqlParameter("@PadreId", padre_id));
        }

        // 显式保存更改，确保插入操作提交
        await context.SaveChangesAsync();
    }                        
    return Ok();            
} 

补充说明 #

• 若使用较新版本的EF Core，SqlQuery已被标记为过时，建议改用FromSqlRaw或FromSqlInterpolated（后者会自动处理参数化），示例：

  var padre = await context.Set<int>()
      .FromSqlRaw("SELECT id FROM pds_etiquetas where nombre='FORMATOS' and tipo = 'Cat'")
      .ToListAsync();
  

• 如果待插入数据量较大，可考虑批量插入工具提升效率，数据量较小时循环插入即可满足需求。

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