Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Executing Stored Procedure in Entity Framework Core 2.0

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.

like image 918
Pradeep H Avatar asked Mar 05 '18 22:03

Pradeep H


People also ask

Can we use stored procedure in Entity Framework Core?

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.

Can you use stored procedures with 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.


2 Answers

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"));
        }
    }
like image 72
Pradeep H Avatar answered Oct 14 '22 16:10

Pradeep H


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

like image 21
grabhints Avatar answered Oct 14 '22 16:10

grabhints