How to utilize output parameters in EF Core 3.0 using ExecuteSqlInterpolatedAsync()

Question

Microsoft doesn't have any current documentation on output parameters, just normal ones. I'm using .Net Core 3.0 with EF Core 3.0 and trying to get an output parameter back from my stored procedure. Code as follows:

var output=""
await _context.Database.ExecuteSqlInterpolatedAsync(
$"EXEC dbo.InsertTest @param1={"test"},@param2={"test2"}, @param3={output});

What I don't know is how to structure the new api in 3.1 to specify an output parameter. I'm wondering if anyone has any documentation on this or has done this in the new update.

Thanks in advance.

Answer 1

Thanks to Eugene for putting me on the right track. In case someone stumbles across this same problem:

To successfully get an output parameter in 3.0, you have to specifically define the output parameter as a SqlParameter() and then include the word "OUT" after your variable.

This is an example of using ExecuteSqlInterpolated()

var output = new SqlParameter();
output.ParameterName = "@ID";
output.SqlDbType = SqlDbType.Int;
output.Direction = ParameterDirection.Output;

await _context.Database.ExecuteSqlInterpolated(
"EXEC dbo.InsertTest @param1={'test'},@param2={'test2'}, @param3={output} OUT");

This is an example of using ExecuteSqlRawAsync()

var output = new SqlParameter();
output.ParameterName = "@ID";
output.SqlDbType = SqlDbType.Int;
output.Direction = ParameterDirection.Output;

await _context.Database.ExecuteSqlRawAsync(
"EXEC dbo.InsertTest @param1={0},@param2={1}, @param3={3} OUT", param1,param2,output);

Answer 2

There is a small chance that creating param3 as DbParameter/SqlParameter with Direction set to Output may work:

var output = new SqlParameter("@param3", DbType.String)
    { Direction = ParameterDirection.Output };
await _context.Database.ExecuteSqlInterpolatedAsync(
    $"EXEC dbo.InsertTest @param1={"test"},@param2={"test2"}, @param3={output}");
Console.WriteLine(output.Value);

But I'm not sure about that from what I can see from the source code and can't check it now with actual DB.

So in the worst case you would just have to drop ExecuteSqlInterpolatedAsync and use some standard way to execute stored procedures that supports output parameters - Entity Framework Core - Using Stored Procedure with output parameters