ASP.NET Core Entity Framework call stored procedure

Question

I am using ASP.NET Core Entity Framework and I would like to call a simple stored procedure.

I created the stored procedure in my migrations like this:

public partial class spGetAvailableCourses : Migration
{
    protected override void Up(MigrationBuilder migrationBuilder)
    {
        var sp = @"CREATE PROCEDURE [dbo].[GetAvailableCourses]
            AS
            BEGIN
                SELECT COUNT(courses.Enrolled) FROM Courses WHERE Courses.Capacity > Courses.Enrolled;
            END";

        migrationBuilder.Sql(sp);
    }

I can call the stored procedure in SQL Server with the following command.

EXEC dbo.GetAvailableCourses

But when I try and call the stored procedure in in my ICourseRepository, it doesn't work, I get minus one returned.

Can someone please tell me the correct of calling the stored procedure? Thanks

public class CourseRepository : ICourseRepository
{
    private readonly DataContext _context;

    public CourseRepository(DataContext context)
    {
        _context = context;
    }
   
    public Task<CoursesAvailableCount> CoursesAvailableCount()
    { 
        var ss = _context.Database.ExecuteSqlRaw("GetAvailableCourses");
        return null;
    }

I also tried

    public async Task<CoursesAvailableCount> CoursesAvailableCount()
    {
        var s = await _context.Database.ExecuteSqlCommandAsync("GetAvailableCourses");
    }

It says "Result must be an entity type. This means that a stored procedure must return all the columns of the corresponding table of an entity." Does this mean its not possible to do it since i am returning an integer? — Great Khan 2016
– Great Khan 2016, Commented Jul 10, 2020 at 14:20
ExecuteSqlRaw returns the number of affected rows. It is the wrong method to call for a count result. — Igor
– Igor, Commented Jul 10, 2020 at 14:28
Why not use a pure EF lambda or linq statement instead? That is very easy to craft based on your sql code in the question. — Igor
– Igor, Commented Jul 10, 2020 at 14:29
As a side note: Should you keep the SP then I would rename GetAvailableCourses to GetAvailableCourseCount as I would expect a list of courses from GetAvailableCourses if I knew nothing else about that SP other than the name. — Igor
– Igor, Commented Jul 10, 2020 at 14:30

AliReza Sabouri · Accepted Answer · 2022-01-18 06:08:43Z

5

In addition to the ExecuteSqlCommand method, the DbContext.Database property provides an API that allows you to perform ADO.NET operations directly. The GetDbConnection method returns a DbConnection object representing the context's underlying connection. From that point, you can revert to the familiar ADO.NET APIs:

using (var command = _context.Database.GetDbConnection ().CreateCommand ())
{
   command.CommandText = "SP_NAME";
   command.CommandType = CommandType.StoredProcedure;
   _context.Database.OpenConnection ();
   using (var result = command.ExecuteReader ())
   {
      if (result.HasRows)
      {
         result.Read ();
         var x = result.GetInt32 (0); // x = your sp count value
      }
   }
}

EDIT (extension example):

public static class EfCoreExtensions
{
    public static int? Execute_SingleValue_SP_AsInt(this AppDbContext context,string SpName)
    {
        using (var command = context.Database.GetDbConnection().CreateCommand())
        {
            command.CommandText = SpName;
            command.CommandType = System.Data.CommandType.StoredProcedure;
            context.Database.OpenConnection();
            using (var result = command.ExecuteReader())
            {
                if (result.HasRows)
                {
                    result.Read();
               var x = result.GetInt32(0); // x = your sp count value
                    return x;
                }
            return null;
            }
        
        }
    }
}

edited Jan 18, 2022 at 6:08

answered Jul 10, 2020 at 15:15

AliReza Sabouri

5,3253 gold badges29 silver badges41 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Great Khan 2016 Over a year ago

Thanks AliReza. That works. I have never used ADO.Net before but it is there a better way of writing result.hasRow? I will read up on it. also should i do a finally i close for the dbconnection?

AliReza Sabouri Over a year ago

EntityFramework Core does not have SqlQuery function so this is your only way to handle custom SPs that can't map to an entity. btw nothing is wrong with using ADO directly. EF is using this internally. you can create an Extention method and use it easily.

Great Khan 2016 Over a year ago

Last question would you recommed using the command.ExecuteReaderAsync() instead of the command.ExecuteReader?

AliReza Sabouri Over a year ago

yes, I always use Async functions when i have a possible blocking task.

AliReza Sabouri Over a year ago

you Asked about HasRow, I recommend watching this video if you are not familiar to DataReaderObject youtube.com/watch?v=xyApEHLvmQU

Timonology · Accepted Answer · 2022-01-27 11:02:25Z

For .net core 3.1, using stored procedures with parameters. Use the below code. It works for me. Enjoy!!!

using (var command = DbContext.Database.GetDbConnection().CreateCommand())
                {
                    command.CommandText = "Users_SP";
                    command.CommandType = CommandType.StoredProcedure;
                    command.Parameters.Add(new SqlParameter(“@usernme”, username));
                    command.Parameters.Add(new SqlParameter("@TransDate", DateTime.Now));
                    DbContext.Database.OpenConnection();
                    using (var result = command.ExecuteReader())
                    {
                        if (result.HasRows)
                        {
                            while (result.Read())
                            {
                                response = Convert.ToString(result["Amount"]);
                            }
                        }
                    }
                }

Collectives™ on Stack Overflow

ASP.NET Core Entity Framework call stored procedure

2 Answers 2

5 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

5 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related