Have a scenario to execute a stored procedure and read the return value in EF Core, that returns a single value.
I tried with this code, but this does not work. I understand that ExecuteSqlCommand
does not work for select and can be used only for update to database.
var test = context.Database.ExecuteSqlCommand("SPName");
The stored procedure has just a select statement like Select 'somevalue'
Looking for any alternative to get data that stored procedure returns.
Stored procedures are one of the key advantages that the Microsoft SQL server provides. For boosting the query performance, the complex query should be written at the database level through stored procedures. Microsoft . NET Core supports calling of raw query and store procedure through entity framework.
You can use stored procedures either to get the data or to add/update/delete the records for one or multiple database tables. EF API creates a function instead of an entity in EDM for each stored procedure and User-Defined Function (UDF) in the target database.
Able to solve my problem with below code. This is based on suggestions given in below replies.
using (var command = context.Database.GetDbConnection().CreateCommand())
{
command.CommandText = "StoredProcedureName";
command.CommandType = CommandType.StoredProcedure;
context.Database.OpenConnection();
var dataReader = command.ExecuteReader();
if (dataReader.Read())
{
string _test = dataReader.GetString(dataReader.GetOrdinal("ColumnName"));
}
}
DbCommand cmd = ctx.Database.GetDbConnection().CreateCommand();
cmd.CommandText = "SPName";
cmd.CommandType = CommandType.StoredProcedure;
if (cmd.Connection.State != ConnectionState.Open)
{
cmd.Connection.Open();
}
return await cmd.ExecuteNonQueryAsync();
Here is a post about that: https://nodogmablog.bryanhogan.net/2016/07/entity-framework-core-and-calling-a-stored-proceduce/#comment-60582
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With