Can't execute reverse engineered Stored procedures

[Miguel1422-Scio]

This is a regression introduced by #1069

Steps to reproduce

Create a database and get the reverse engineered model

CREATE DATABASE [ConUniv]
GO
USE [ConUniv]
GO
CREATE TABLE [dbo].[Course](
	[Id] [uniqueidentifier] NOT NULL,
	[Title] [varchar](50) NOT NULL,
	[Credits] [int] NOT NULL,
	[DepartmentID] [uniqueidentifier] NOT NULL,
	[Budget] [money] NOT NULL,
 CONSTRAINT [PK_Course] PRIMARY KEY CLUSTERED 
(
	[Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

CREATE PROCEDURE [dbo].[spCourseCount]	
AS
BEGIN
	SET NOCOUNT ON;
	Select Count(*) CourseCount From Course
END
GO

When I try to execute an SP I get the following error

Unhandled exception. Microsoft.Data.SqlClient.SqlException (0x80131904): The parameterized query '(@returnValue int output)EXEC @returnValue = [dbo].[spCourseCoun' expects the parameter '@returnValue', which was not supplied.
   at Microsoft.Data.SqlClient.SqlCommand.<>c.<ExecuteDbDataReaderAsync>b__169_0(Task`1 result)
   at System.Threading.Tasks.ContinuationResultTaskFromResultTask`2.InnerInvoke()
   at System.Threading.Tasks.Task.<>c.<.cctor>b__277_0(Object obj)
   at System.Threading.ExecutionContext.RunInternal(ExecutionContext executionContext, ContextCallback callback, Object state)
--- End of stack trace from previous location ---
   at System.Threading.ExecutionContext.RunInternal(ExecutionContext executionContext, ContextCallback callback, Object state)
   at System.Threading.Tasks.Task.ExecuteWithThreadLocal(Task& currentTaskSlot, Thread threadPoolThread)
--- End of stack trace from previous location ---
   at Microsoft.EntityFrameworkCore.Storage.RelationalCommand.ExecuteReaderAsync(RelationalCommandParameterObject parameterObject, CancellationToken cancellationToken)
   at Microsoft.EntityFrameworkCore.Storage.RelationalCommand.ExecuteReaderAsync(RelationalCommandParameterObject parameterObject, CancellationToken cancellationToken)
   at Microsoft.EntityFrameworkCore.Query.Internal.FromSqlQueryingEnumerable`1.AsyncEnumerator.InitializeReaderAsync(DbContext _, Boolean result, CancellationToken cancellationToken)
   at Microsoft.EntityFrameworkCore.Storage.ExecutionStrategy.ExecuteImplementationAsync[TState,TResult](Func`4 operation, Func`4 verifySucceeded, TState state, CancellationToken cancellationToken)
   at Microsoft.EntityFrameworkCore.Storage.ExecutionStrategy.ExecuteImplementationAsync[TState,TResult](Func`4 operation, Func`4 verifySucceeded, TState state, CancellationToken cancellationToken)
   at Microsoft.EntityFrameworkCore.Query.Internal.FromSqlQueryingEnumerable`1.AsyncEnumerator.MoveNextAsync()
   at Microsoft.EntityFrameworkCore.EntityFrameworkQueryableExtensions.ToListAsync[TSource](IQueryable`1 source, CancellationToken cancellationToken)
   at Microsoft.EntityFrameworkCore.EntityFrameworkQueryableExtensions.ToListAsync[TSource](IQueryable`1 source, CancellationToken cancellationToken)
   at EFTools.Models.DbContextExtensions.SqlQueryAsync[T](DbContext db, String sql, Object[] parameters, CancellationToken cancellationToken) in C:\source\repos\EFTools\EFTools\Models\DbContextExtensions.cs:line 28
   at EFTools.Models.ConunivContextProcedures.spCourseCountAsync(OutputParameter`1 returnValue, CancellationToken cancellationToken) in C:\source\repos\EFTools\EFTools\Models\ConunivContextProcedures.cs:line 58
   at EFTools.Program.Main(String[] args) in C:\source\repos\EFTools\EFTools\Program.cs:line 12
   at EFTools.Program.<Main>(String[] args)
ClientConnectionId:e212810a-f4e2-4eb2-bb32-ce71b81c9836
Error Number:8178,State:1,Class:16

class Program
    {
        static async Task Main(string[] args)
        {
            var context = new ConunivContext();
            var result = await context.Procedures.spCourseCountAsync();
            Console.WriteLine(result.Single().CourseCount);
        }
    }

It works fine if I change manually the Direction to System.Data.ParameterDirection.Output in spCourseCountAsync

public virtual async Task<List<spCourseCountResult>> spCourseCountAsync(OutputParameter<int> returnValue = null, CancellationToken cancellationToken = default)
        {
            var parameterreturnValue = new SqlParameter
            {
                ParameterName = "returnValue",
                Direction = System.Data.ParameterDirection.Output, // InputOutput
                SqlDbType = System.Data.SqlDbType.Int,
            };

            var sqlParameters = new []
            {
                parameterreturnValue,
            };
            var _ = await _context.SqlQueryAsync<spCourseCountResult>("EXEC @returnValue = [dbo].[spCourseCount]", sqlParameters, cancellationToken);

            returnValue?.SetValue(parameterreturnValue.Value);

            return _;
        }

Further technical details

EF Core Power Tools version: 2.5.731

Database engine: SQL Server

Visual Studio version: Visual Studio 2019 16.11.2

[ErikEJ]

Thanks, reverted, must think of a better way to support input/output

[whschultz]

I looked at your attempted fix, and it wouldn't have worked for the desired reason anyway. Why? Because you weren't supplying a value.

Conceptual example:

a SPROC that saves an object to the database. The ID of the item may or may not be supplied in an output parameter. If it is supplied, update the existing element in the database. If it is not supplied, create a new one and return the new ID via the output parameter.

[ErikEJ]

I am working on a better fix now! 😀

[whschultz]

Sorry. Just realized I intended to post the above on the other issue.

[ErikEJ]

I will create a PR and you can review it!

[whschultz]

Urgh. I'm no expert here. I'm several years out of date on my EF experience and just got caught up in the past few days from reviewing a team member's PR. I just got DB access today and haven't run my team member's code. But we can potentially work together and get it tested.

If it helps, I found this discussion of what happens with the direction parameter as of 2016:

https://www.codeproject.com/Articles/1151843/Input-Output-and-Input-Output-SqlParameter-Behavio

[whschultz]

Migrated discussion back to the other issue.