EF6 stored procedure - output direction parameter-CodePudding

What's the right way to run a EF6 stored procedure with output direction parameter?

List<MySqlParameter> parms = new()
{
   new MySqlParameter { ParameterName = "@FilterJson", Value = JsonConvert.SerializeObject(input) },
   new MySqlParameter { ParameterName = "@OutputJson", Direction = System.Data.ParameterDirection.Output }
};

var response = ReportingDetail.FromSqlRaw(
   @"CALL p_detail_get(@FilterJson, @OutputJson)",
   parms.ToArray()
);

This code throws an error:

MySqlConnector.MySqlException: 'Only ParameterDirection.Input is supported when CommandType is Text (parameter name: @OutputJson)'

Expected output is JSON, something like: {"rows_count": 7, "actual_page": 1, "pages_count": 1}

The problem is not with a connection, other procedures with no output direction works fine. Defining a type of @OutputJson does not fix the issue - MySqlDbType = MySqlDbType.JSON or DbType = System.Data.DbType.Object.

Thank you for any help

CodePudding user response：

According to the MySqlConnector disscussion, it is not possible to return output direction parameters through it. You can try to write ADO.NET code to omit this restriction.

CodePudding user response：

The MySQL text protocol doesn't support output parameters; see this discussion.

To work around this, replace your output parameter with a SELECT statement; I'm dropping down to plain ADO.NET here, as I'm not sure if/how to do this with EF Core:

using var command = connection.CreateCommand();
command.CommandText = @"
SET @OutputJson = NULL;
CALL p_detail_get(@FilterJson, @OutputJson);
SELECT @OutputJson;";
command.Parameters.AddWithValue("@FilterJson", JsonConvert.SerializeObject(input));
var outputJson = (string) command.ExecuteScalar();