Executing Stored Procedure in Entity Framework Core 2.0

Question

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.

Solution 1

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"));
        }
    }

Solution 2

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

Solution 3

Have a look over the documentation from MS: https://docs.microsoft.com/en-us/ef/core/querying/raw-sql

var blog = context.Blogs
    .FromSql("EXECUTE dbo.GetMostPopularBlogs")
    .SingleOrDefault();

They have a git repo full of examples: https://github.com/aspnet/EntityFramework.Docs/blob/master/samples/core/Querying/Querying/RawSQL/Sample.cs

To utilise stored procedures in EF Core, check out this video: https://www.youtube.com/watch?v=bX3BPSbvofE

Comments

[Pradeep H]

FromSql works only on entities, In my case I have no entities to query data. it is SP in Database that returns a value

[nologo]

msdn.microsoft.com/en-us/library/… the return result of that method is int32.

[nologo]

if u aren't using entities.. then you don't need EF. a quick google found this video. youtube.com/watch?v=bX3BPSbvofE

[Pradeep H]

Thank you. Will check out that. But it was easy to execute SP in in .Net Framework based EF, checking if there is similar feature in .Net core

[Pradeep H]

the video link helped and solved the problem. Thank you.

[Chris Rosete]

Having this error 'DbSet<sp_SearchUsers>' does not contain a definition for 'FromSql' and no accessible extension method 'FromSql' accepting a first argument of type 'DbSet<sp_SearchUsers>' could be found. I am using EF 5