Pomelo.entityframeworkcore.mysql: Ef-core 3.0 stored-procedure multiple parameters

[shunjid]

I am working with a MySql Database. In my database there is a stored procedure in which I am inserting some data and then returning this whole table.

My stored procedure code is something like:

DELIMITER $$
    Create Procedure InsertDepartments(DName longtext,DLoc longtext)
BEGIN
    INSERT INTO Departments (DepartmentName, DepartmentLocation) 
    VALUES (Dname, DLoc);
    SELECT * FROM Departments ORDER By Departments.DepartmentId DESC;
END$$

As you can see I am trying to take two parameters DName and DLoc. Now in my controller in backend I have tried with something :

public JsonResult InsertDepartment([FromBody]Department department)
{
     try
     {
        var deptName = new MySqlParameter("Dname", department.DepartmentName);
        var deptLoc = new MySqlParameter("DLoc", department.DepartmentLocation);

        var departments = _context
          .Departments
          .FromSqlRaw("EXECUTE InsertDepartments @Dname , @DLoc", new object[] {deptName, deptLoc})
          .ToList();
     return Json(departments);
    }
    catch (Exception e)
    {
       return Json(e.Message);
    }
}

But this is failing somehow. The error log returned in PostMan is:

"You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near ''CSE' , 'DT5'' at line 1"

System.NotSupportedException: Specified method is not supported.
   at System.Text.Json.JsonSerializer.WriteDictionary[TProperty](JsonConverter`1 converter, JsonSerializerOptions options, WriteStackFrame& current, Utf8JsonWriter writer)
   at System.Text.Json.JsonPropertyInfoNotNullable`4.OnWriteDictionary(WriteStackFrame& current, Utf8JsonWriter writer)
   at System.Text.Json.JsonPropertyInfo.WriteDictionary(WriteStack& state, Utf8JsonWriter writer)
   at System.Text.Json.JsonSerializer.HandleDictionary(JsonClassInfo elementClassInfo, JsonSerializerOptions options, Utf8JsonWriter writer, WriteStack& state)
   at System.Text.Json.JsonSerializer.HandleObject(JsonPropertyInfo jsonPropertyInfo, JsonSerializerOptions options, Utf8JsonWriter writer, WriteStack& state)
   at System.Text.Json.JsonSerializer.WriteObject(JsonSerializerOptions options, Utf8JsonWriter writer, WriteStack& state)
   at System.Text.Json.JsonSerializer.Write(Utf8JsonWriter writer, Int32 originalWriterDepth, Int32 flushThreshold, JsonSerializerOptions options, WriteStack& state)
   at System.Text.Json.JsonSerializer.WriteAsyncCore(Stream utf8Json, Object value, Type inputType, JsonSerializerOptions options, CancellationToken cancellationToken)
   at Microsoft.AspNetCore.Mvc.Infrastructure.SystemTextJsonResultExecutor.ExecuteAsync(ActionContext context, JsonResult result)
   at 

> Microsoft.AspNetCore.Mvc.Infrastructure.SystemTextJsonResultExecutor.ExecuteAsync(ActionContext context, JsonResult result)
>    at Microsoft.AspNetCore.Mvc.Infrastructure.ResourceInvoker.<InvokeResultFilters>g__Awaited|27_0(ResourceInvoker invoker, Task lastTask, State next, Scope scope, Object state, Boolean isCompleted)
>    at Microsoft.AspNetCore.Mvc.Infrastructure.ResourceInvoker.<InvokeFilterPipelineAsync>g__Awaited|19_0(ResourceInvoker invoker, Task lastTask, State next, Scope scope, Object state, Boolean isCompleted)
>    at Microsoft.AspNetCore.Mvc.Infrastructure.ResourceInvoker.<InvokeAsync>g__Logged|17_1(ResourceInvoker invoker)
>    at Microsoft.AspNetCore.Routing.EndpointMiddleware.<Invoke>g__AwaitRequestTask|6_0(Endpoint endpoint, Task requestTask, ILogger logger)
>    at Microsoft.AspNetCore.Authorization.AuthorizationMiddleware.Invoke(HttpContext context)
>    at Microsoft.AspNetCore.Diagnostics.DeveloperExceptionPageMiddleware.Invoke(HttpContext context)

[lauxjpn]

MySQL (and MariaDB) use CALL to run a stored procedure, not EXECUTE, and wrap parameters with parenthesis (see the MySQL docs and tutorial):

```c#
var deptName = new MySqlParameter("@Dname", department.DepartmentName);
var deptLoc = new MySqlParameter("@DLoc", department.DepartmentLocation);

var departments = _context
.Departments
.FromSqlRaw("CALL InsertDepartments(@Dname, @DLoc)",
new object[] {deptName, deptLoc})
.ToList();

Also make sure to use the right parameter name syntax and use the same spelling in your `FromSqlRaw()` call.

Using [interpolated SQL](https://docs.microsoft.com/en-us/ef/core/querying/raw-sql#passing-parameters) is simpler:

```c#
var departments = _context
  .Departments
  .FromSqlInterpolated(
    $"CALL InsertDepartments({department.DepartmentName}, {department.DepartmentLocation});");

[shunjid]

@lauxjpn Closing the issue. Very well explained. Thank you so much :+1: