EF6 - ExecuteSqlCommandAsync - Get return paramete

Question

I have the following code:

object[] parameters =
    {
        new SqlParameter("@val1", val1),
        new SqlParameter("@val2", val2),
        new SqlParameter
        {
            ParameterName = "@retVal",
            SqlDbType = SqlDbType.Int,
            Direction = ParameterDirection.ReturnValue,
            Value = -1
        }
    };

    await context.Database.ExecuteSqlCommandAsync("EXEC @retVal = Example_SP @val1, @val2", parameters);

The SP I'm using is fine and returns a value in SQL MS fine. But when I execute it using EF I am told I 'must declare the scalar variable @retVal'. Isn't that what my SqlParameter does??

I've tried removing the '@' sign form the parameters, as some have suggested elsewhere, but as I understand it the '@' sign is optional and makes no difference anyway.

How do I get the return value from the SP without causing errors, using ExecuteSqlCommandAsync?

Thank you!

Answer 1

You need to use ParameterDirection.Output instead of ParameterDirection.ReturnValue.

Answer 2

TL&DR: Create an Output Variable to house the 'returning' ID query. This answer is related to a POSTGRES database.

Rationale: The ExecuteSqlCommand function returns the number of rows affected. You need an additional output which is the 'returning' id that is inserted. Therefore, you need to provide an OUTPUT parameter, which can hold this value.

Solution

Use the System.Data.ParameterDirection.Output for the Direction within NpgsqlParameter.

  NpgsqlParameter idOut = new NpgsqlParameter
        {
            Direction = System.Data.ParameterDirection.Output
        };
  await _context.Database.ExecuteSqlCommandAsync($"INSERT INTO mytable (myid, create_time, modified_time) VALUES ({mytableid}, now(),now()) RETURNING myid;", idOut);