Execute async non selectable stored procedure in EF Core 2.1

asp.net-core asp.net-core-2.0 asp.net-core-webapi entity-framework-core entity-framework-core-2.1

Question

I'm trying to execute async non selectable stored procedure in EF Core 2.1.

  return Ok(
              await db.Set()
              .FromSql($"EXECUTE PROCEDURE MY_PROC({id})")
              .ToListAsync());

Error:

The type arguments for method 'DbContext.Set()' cannot be inferred from the usage. Try specifying the type arguments explicitly

I also tried:

return Ok(
             await db.MYDB
              .FromSql($"EXECUTE PROCEDURE MY_PROC({id})")              
             .ToListAsync());

Error:

500 (Internal Server Error) in console

Then I tried creating a static method:

public static async Task ExecSqlNonQuery(Entities db, string sql)
{
     using (var com = db.Database.GetDbConnection().CreateCommand())
     {
         com.CommandText = sql;
         com.CommandType = CommandType.StoredProcedure;
         db.Database.OpenConnection();
         await com.ExecuteNonQueryAsync();
     };
}
....
return Ok( await ExecSqlNonQuery(db,$"EXECUTE PROCEDURE MY_PROC({id})");   

but with this code, I get:

Argument 1: cannot convert from 'void' to 'object'

only works on non async and old-fashioned way:

public static  bool ExecSqlNonQuery(Entities db, string sql)
{
    try
    {
        using (var com = db.Database.GetDbConnection().CreateCommand())
        {
            com.CommandText = sql;
            com.CommandType = CommandType.StoredProcedure;

            db.Database.OpenConnection();
            com.ExecuteNonQuery();

            return true;
        };
    }
    catch (Exception)
    {
        return false;
    }
}
......
return Ok(ExecSqlNonQuery(db, $"EXECUTE PROCEDURE MY_PROC({id})"););

Any help? Thanks

1
1
8/3/2018 8:28:56 AM

Accepted Answer

FromSql method is for record set (query) returning SQL commands. For other types of commands you should use some of the ExecuteSqlCommand or ExecuteSqlCommandAsync method overloads.

Executes the given SQL against the database and returns the number of rows affected.

For instance (assuming the SQL syntax is correct):

await db.Database.ExecuteSqlCommandAsync($"EXECUTE PROCEDURE MY_PROC({id})");
return Ok();
2
8/3/2018 6:52:56 AM


Related Questions





Related

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow