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
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();