如何在.NET Core 6 API中用EF调用SQL Server存储过程并先检查返回值

问题描述 #

我在.NET Core 6的API中将Equipment模型简化为仅包含Equipment_Id和Description两列。调用POST接口新增设备时，SQL Server存储过程返回0或2（成功场景）时运行正常，但返回值为1（如“设备已存在”这类错误）时，由于存储过程未返回结果集，EF Core尝试将结果转换为List<Equipment>时会触发如下异常：

System.InvalidOperationException: The required column 'Equipment_Id' was not present in the results of a 'FromSql' operation.

我需要在异常触发前先检查存储过程的返回值（通过输出参数），再决定是否转换结果集，请问该如何实现？

原代码示例：

try
{
    // snipped dbParameters creation
    string CommandText = "EXEC @ReturnValue=sp_API_Model_Equipment_WRITE @Equipment_Id=@Equipment_Id OUTPUT,@Description=@Description,@ErrMsg=@ErrMsg OUTPUT";

    var results = await _DbContext.Equipment.FromSqlRaw(CommandText, dbParameters.ToArray()).ToListAsync(); 

    // If the stored procedure detects a problem and returns value 1 (error), 
    // there is no resultset and an exception is generated:
    //
    // Microsoft.EntityFrameworkCore.Query: Error: 
    // An exception occurred while iterating over the results of a query for context type 'myAPI.Data.DBContext'.
    // System.InvalidOperationException: The required column 'Equipment_Id' was not present in the results of a 'FromSql' operation.
    // at Microsoft.EntityFrameworkCore.Query.Internal.FromSqlQueryingEnumerable`1.BuildIndexMap(IReadOnlyList`1 columnNames, DbDataReader dataReader)
    // at Microsoft.EntityFrameworkCore.Query.Internal.FromSqlQueryingEnumerable`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.FromSqlQueryingEnumerable`1.AsyncEnumerator.MoveNextAsync()
    //

    int ReturnValue = (int)paramReturnValue.Value;

    switch (ReturnValue)
    {
        case 0: // Success
            if (results.Count == 0)
                return NotFound();  // 404

            return CreatedAtAction("GetEquipmentById", new { Equipment_Id = results[0].SerialNum }, results[0]);  // 201 Created

        case 1: // Error
            return BadRequest(paramErrMsg.Value);

        case 2: // Warning (Already Exists)
            if (results.Count == 0)
                return NotFound();  // 404 (Internal Failure)

            return Ok(results[0]);  // 200 OK

        case 99: // Exception
            return StatusCode(StatusCodes.Status500InternalServerError, "Internal exception occurred");
    }

    return BadRequest($"Unknown ReturnValue ${ReturnValue}.");
}
catch (Exception ex)
{
    clsDebug.WriteDebugExc(ex);

    return StatusCode(StatusCodes.Status500InternalServerError, "Internal exception occurred.");
}

解决方案 #

核心思路是先执行存储过程，获取返回值和输出参数后，再根据返回值判断是否需要读取并映射结果集，避免EF Core在无结果集时强行映射实体导致异常。可以通过直接使用ADO.NET的DbCommand执行命令，而非EF Core的FromSqlRaw，以此获得更灵活的执行流程控制。

修改后的代码示例 #

try
{
    // 假设paramReturnValue、paramErrMsg等参数已提前创建配置完成
    using var command = _DbContext.Database.GetDbConnection().CreateCommand();
    command.CommandText = "EXEC @ReturnValue=sp_API_Model_Equipment_WRITE @Equipment_Id=@Equipment_Id OUTPUT,@Description=@Description,@ErrMsg=@ErrMsg OUTPUT";
    command.Parameters.AddRange(dbParameters.ToArray());

    // 打开数据库连接（EF Core会自动管理连接生命周期）
    await _DbContext.Database.OpenConnectionAsync();
    var dataReader = await command.ExecuteReaderAsync();

    List<Equipment> results = new();
    // 仅当存在结果集时，才尝试映射为Equipment实体
    if (dataReader.HasRows)
    {
        // 复用EF Core的实体映射逻辑，避免手动读取列值
        var entityType = _DbContext.Model.FindEntityType(typeof(Equipment));
        var materializer = _DbContext.CreateMaterializer<Equipment>(entityType);
        
        while (await dataReader.ReadAsync())
        {
            results.Add(materializer(dataReader));
        }
        // 前进到下一个结果集，确保后续能正确获取返回值和输出参数
        await dataReader.NextResultAsync();
    }

    // 关闭DataReader后才能正确读取输出参数和返回值
    await dataReader.CloseAsync();
    int returnValue = (int)paramReturnValue.Value;
    string errorMsg = paramErrMsg.Value?.ToString() ?? "未知错误";

    switch (returnValue)
    {
        case 0: // 新增成功
            if (results.Count == 0)
                return NotFound();

            return CreatedAtAction("GetEquipmentById", new { Equipment_Id = results[0].Equipment_Id }, results[0]);

        case 1: // 错误（无结果集）
            return BadRequest(errorMsg);

        case 2: // 设备已存在（返回现有设备信息）
            if (results.Count == 0)
                return NotFound();

            return Ok(results[0]);

        case 99: // 存储过程内部异常
            return StatusCode(StatusCodes.Status500InternalServerError, "系统内部异常");
    }

    return BadRequest($"未知返回值：{returnValue}");
}
catch (Exception ex)
{
    clsDebug.WriteDebugExc(ex);
    return StatusCode(StatusCodes.Status500InternalServerError, "发生内部异常，请联系管理员");
}

关键说明 #

1. 直接使用DbCommand：绕过EF Core的FromSqlRaw自动映射逻辑，手动控制结果集读取时机，避免无结果集时的映射异常。
2. 复用EF Core映射逻辑：通过CreateMaterializer方法复用EF Core的实体映射规则，无需手动编写列值读取代码，保持代码一致性。
3. 参数读取时机：必须关闭DbDataReader后，才能正确获取存储过程的返回值和输出参数，否则可能无法读取到最新的参数值。

可选方案（需修改存储过程） #

如果允许修改存储过程，可以确保无论返回值是什么，都返回一个符合Equipment结构的结果集（比如返回空结果集，或者包含错误标识的实体），这样就能继续使用FromSqlRaw方法，但这种方式需要同步调整数据库层面的逻辑。

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