How to call stored procedure in API using EF/SQL Server and check RetVal before processing ResultSet?

Question

I've reduced a .NET Core 6 API for an Equipment model to just two columns for testing: Equipment_Id and Description. During the POST (Add), the SQL Server stored procedure will return value 0 or 2 on success and everything works fine.

If I get a return value of 1 (error) with an error message (ex: "Equipment already exists"), the conversion to Equipment fails because there is no result set and an exception occurs. I want to examine the return value (dbParam) within generating the exception.

How do I execute the stored procedure, examine the return value and then convert the result set to a List ?

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.");
}

David Browne - Microsoft · Answer

You could try returning an empty resultset in that case, something like:

SET NOCOUNT ON 
EXEC @ReturnValue=sp_API_Model_Equipment_WRITE @Equipment_Id=@Equipment_Id OUTPUT,@Description=@Description,@ErrMsg=@ErrMsg OUTPUT";

if @ReturnValue = 1
begin
  select * from equipment where 1=0;
end

How to call stored procedure in API using EF/SQL Server and check RetVal before processing ResultSet?

1 Answers