EF6 - ExecuteSqlCommandAsync - Get return parameter (declare scalar variable error)

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

Let me introduce some extended solution.

In this working example as you see there is a stored procedure that has output parameter and async/await C# code that shows how to accept this output single value.

I hope it will help to understand the approach and will be an extended answer on your question.

C# WinForm

 private async void Button1_Click(object sender, EventArgs e)
        {
            await CleanDevicePositions();
        }

        public async Task CleanDevicePositions()
        {
            try
            {
                var endDate = DateTime.Now.AddDays(-120).ToString("yyyyMMdd");
                using (var db = new TechServiceEntities())
                {
                    db.Database.CommandTimeout = 18000;

                    object[] parameters = {
                          new SqlParameter("@MaxToDeleteInp", 1000),
                          new SqlParameter("@BatchSizeInp", 100),
                          new SqlParameter("@ToEndDateInp", endDate),
                          new SqlParameter
                            {
                                ParameterName = "@Records",
                                SqlDbType = SqlDbType.Int,
                                Value = -1,
                                Direction = ParameterDirection.Output
                            }
                    };

                    // For result description have a look at https://learn.microsoft.com/en-us/sql/relational-databases/stored-procedures/return-data-from-a-stored-procedure?view=sql-server-ver15

                    var result = await db.Database.
                        ExecuteSqlCommandAsync($"EXEC dbo.DeleteOldDevicePositions @MaxToDeleteInp, @BatchSizeInp, @ToEndDateInp, @Records OUTPUT", parameters)
                        .ConfigureAwait(false); ;

                    var retValue = (parameters[3] as SqlParameter).Value;
                    BeginInvokeLambda(listBox1, () => { listBox1.Items.Add(retValue); });
                    BeginInvokeLambda(listBox1, () => { listBox1.Items.Add(result); });
                }
            }
            catch (SqlException ex)
            {
                Debug.WriteLine(ex);
                BeginInvokeLambda(listBox1, () => { listBox1.Items.Add(ex.ToString()); });
                BeginInvokeLambda(listBox1, () => { listBox1.Items.Add(ex.Message); });
            }
            catch (Exception ex)
            {
                Debug.WriteLine(ex);
                BeginInvokeLambda(listBox1, () => { listBox1.Items.Add(ex.ToString()); });
                BeginInvokeLambda(listBox1, () => { listBox1.Items.Add(ex.Message); });
            }
        }

        private static IAsyncResult BeginInvokeLambda(Control control, Action action)
        {
            if (!control.IsDisposed)
            {
                return control.BeginInvoke(action, null);
            }
            return null;
        }

TSQL This SP just deletes some records by defined batch size of its amount.

ALTER PROCEDURE [dbo].[DeleteOldDevicePositions] 
    @MaxToDeleteInp int = 1000, -- Max amount of records to delete
    @BatchSizeInp int = 100, -- it is
    @ToEndDateInp datetime, -- Delete until this datetime
    @Records int OUTPUT  
AS
BEGIN
 
    SET NOCOUNT ON;
   
    DECLARE @MaxToDelete int ,  @BatchSize int , @ToEndDate datetime;
    SET @MaxToDelete = @MaxToDeleteInp;
    SET @BatchSize = @BatchSizeInp;
    SET @ToEndDate = @ToEndDateInp;

    DECLARE @TransactionInterval tinyint = 5, @FactDeleted int = 0;
    DECLARE @counter int = 1;
    DECLARE @s datetime, @e datetime, @r int = 1;    
    SELECT @s = MIN(AtTime) FROM dbo.DevicePositions;
    BEGIN TRANSACTION;
    WHILE (@r > 0)
    BEGIN
      IF @r % @TransactionInterval = 1
      BEGIN
        COMMIT TRANSACTION;
        BEGIN TRANSACTION;
      END
      DELETE TOP (@BatchSize) FROM DevicePositions WHERE AtTime >= @s AND AtTime <= @ToEndDate;
      SET @FactDeleted = @FactDeleted +@BatchSize;
      SET @r = @@ROWCOUNT;
      SET @counter = @counter + 1;
      IF @FactDeleted >= @MaxToDelete 
         BREAK;
    END
    IF @@TRANCOUNT > 0
    BEGIN
      COMMIT TRANSACTION;
      IF @counter % 10  = 0  -- or maybe 100 or 1000
      BEGIN 
        CHECKPOINT; 
      END
    END 
     
    SELECT @Records = A.Records FROM (
            SELECT OBJECT_NAME(object_id) as ID, SUM(row_count) AS Records FROM sys.dm_db_partition_stats WHERE 
            object_id = OBJECT_ID('DevicePositions') AND index_id < 2
            GROUP BY OBJECT_NAME(object_id) ) A 
   RETURN  @Records;
END

App Here you can see the number of records after deleting.

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);

Answer 3

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