Use Stored Procedure in Entity Framework Core

This tutorial will guide you on how you can use Stored Procedure in Entity Framework Core. In my previous article, I made a full crud tutorial on using Entity Framework Core. Entity Framework can build and execute SQL commands automatically. EF handles all the connections and interactions that we need in our database.

What is Stored Procedure?

A stored procedure is a set of Structured Query Language (SQL) statements with an assigned name, stored in a relational database management system as a group to be reused and shared by multiple applications. If you have a query used numerous times, you can save it as stored procedures and call it in any programs you have.

Now that we already have an overview of a stored procedure. Let’s start by creating a new ASP.NET Core API.

Note: I created this project using Visual Studio Code. Make sure you have it installed on your machine. If not, Visual Studio would also be fine.

I. Create new ASP.NET Core Web Api

1. Create a new folder for your project.
2. Open a command prompt and navigate to your folder location using the cd command. (ex. cd folder name)
3. Type “code .”, this will open your project directory inside the visual studio code.
4. Open Visual Studio Code Terminal and create a new ASP.NET Core web API project using this command.

dotnet new webapi --name SPCoreApi

5. You can remove sample controller WeatherForecastController to start a clean project solution. See the image below.

This is the final folder structure for this tutorial. I will also attached github source code.

II. Install Required Nuget Packages

Now that we have created a new Web API project. We need to add Entity Framework Packages. Navigate to the Visual Studio code terminal and execute the commands below to download the required packages.

dotnet add package Microsoft.AspNetCore.Identity.EntityFrameworkCore
dotnet add package Microsoft.EntityFrameworkCore
dotnet add package Microsoft.EntityFrameworkCore.Design
dotnet add package Microsoft.EntityFrameworkCore.SqlServer

If you are using Visual Studio IDE you can add this packages using Nuget Package Manager.

You can also use this video to guide you on creating a new webapi in ASP.NET Core

II. Setup Database Connection and AppDBContext

1. Add your SQL database connection string inside your appsettings.json.

"ConnectionStrings": {
    "default": "Data Source=DESKTOP-4DU98BI\SQLEXPRESS01;User ID=username;Password=password;Connect Timeout=30;Encrypt=False;TrustServerCertificate=False;ApplicationIntent=ReadWrite;MultiSubnetFailover=False"
  },

2. Configure your application to Use SQL Server in your Startup.cs. Put the code snippet below inside ConfigureServices method.

  services.AddDbContext<AppDBContext>(options =>
            {
                options.UseSqlServer(
                    Configuration.GetConnectionString("default"));
            });

3. Create AppDBContext class. AppDBContext will handle connections to our database using Entity Framework Core.

using Microsoft.EntityFrameworkCore;

namespace SPCoreApi.Models
{
    public class AppDBContext : DbContext
    {
        public AppDBContext(DbContextOptions<AppDBContext> options)
             : base(options)
        {

        }
        public DbSet<MemberProfile> MemberProfiles { get; set; }
    }
}

4. Create MemberProfile Class. This model is the table columns’ properties that we want to create later when we execute the migration and database update command.

using System.ComponentModel.DataAnnotations;

namespace SPCoreApi.Models
{
    public class MemberProfile
    {
        [Key]
        public int Id { get; set; }
        public string EmailAddress { get; set; }
        public string UserName { get; set; }
        public string ContactNumber { get; set; }
        public string Status { get; set; }
    }
}

III. Create Migrations for member table and Stored Procedure

This time we are ready to create and run migrations. This command will create our database for us. This is the Code first approach of Entity Framework.

1. Migration » create_table_member:

dotnet ef migrations add create_table_member

This command will create a migration file that contains SQL command for creating tables. Migration has code similar to the snippet below.

using Microsoft.EntityFrameworkCore.Migrations;

namespace SPCoreApi.Migrations
{
    public partial class create_table_member : Migration
    {
        protected override void Up(MigrationBuilder migrationBuilder)
        {
            migrationBuilder.CreateTable(
                name: "MemberProfiles",
                columns: table => new
                {
                    Id = table.Column<int>(type: "int", nullable: false)
                        .Annotation("SqlServer:Identity", "1, 1"),
                    EmailAddress = table.Column<string>(type: "nvarchar(max)", nullable: true),
                    UserName = table.Column<string>(type: "nvarchar(max)", nullable: true),
                    ContactNumber = table.Column<string>(type: "nvarchar(max)", nullable: true),
                    Status = table.Column<string>(type: "nvarchar(max)", nullable: true)
                },
                constraints: table =>
                {
                    table.PrimaryKey("PK_MemberProfiles", x => x.Id);
                });
        }

        protected override void Down(MigrationBuilder migrationBuilder)
        {
            migrationBuilder.DropTable(
                name: "MemberProfiles");
        }
    }
}

2. Migration » Stored Procedure

Stored procedure is not created automatically. We need to create and test a stored procedure inside an SQL Server Query Editor. Assuming that we have already created and tested the Stored Procedure that we are going to use on our EF Web Api. This is how we add it in a Code First Approach.

2.1 First, we need to initialize an empty migration. To do that, execute the code snippet below.

dotnet ef migrations add create_sp_migration

This will create an empty migration file containing the code snippet below.

using Microsoft.EntityFrameworkCore.Migrations;

namespace SPCoreApi.Migrations
{
    public partial class create_sp_migration : Migration
    {
        protected override void Up(MigrationBuilder migrationBuilder)
        {
           
        }

        protected override void Down(MigrationBuilder migrationBuilder)
        {
              
        }
    }
}

Migration created two methods:

• Up » This will implement the data model changes for a migration
• Down » This will be executed when you want to roll back the update

2.2 On the empty Migration file, we will add our create stored procedure command on the Up method. I have created two stored procedures for this tutorial; one is to insert member data, and the other one to select all members. See the snippet below.

Insert Member data » sp_CreateMember

This SQL Stored procedure will insert data to memberprofiles table and return integer value(100 for success and 500 for Error)

 migrationBuilder.Sql(@"CREATE OR ALTER PROCEDURE sp_CreateMember(
                                        @emailAddress VARCHAR(100), 
                                        @userName VARCHAR(100), 
                                        @contactNumber VARCHAR(100), 
                                        @Status VARCHAR(100), 
                                        @retVal int out)
                                    AS
                                    BEGIN

                                    INSERT INTO MemberProfiles(
                                        EmailAddress, 
                                        UserName, 
                                        ContactNumber, 
                                        [Status]) 
                                        VALUES(@emailAddress, @userName, @contactNumber, @Status);

                                        ---check for number of rows affected
                                        IF(@@ROWCOUNT > 0)
                                        BEGIN
                                            SET @retVal = 200 -- command was executed successfully
                                        END
                                        ELSE
                                        BEGIN
                                            SET @retVal = 500  -- nothing is updated
                                        END
                                    END");

Select All member » sp_GetMembers

This will return all member from memberprofiles table.

migrationBuilder.Sql(@"CREATE OR ALTER PROCEDURE sp_GetMembers
                                    AS
                                    BEGIN
                                        Select * FROM MemberProfile
                                    END");

2.3 To reverse that command we need to add drop stored procedure command on the down method.

 migrationBuilder.Sql("drop procedure sp_createMember");
 migrationBuilder.Sql("drop procedure sp_GetMembers");

Overall, this is how our create_sp_migration will look like.

using Microsoft.EntityFrameworkCore.Migrations;

namespace SPCoreApi.Migrations
{
    public partial class create_sp_migration : Migration
    {
        protected override void Up(MigrationBuilder migrationBuilder)
        {
           migrationBuilder.Sql(@"CREATE OR ALTER PROCEDURE sp_CreateMember(
                                        @emailAddress VARCHAR(100), 
                                        @userName VARCHAR(100), 
                                        @contactNumber VARCHAR(100), 
                                        @Status VARCHAR(100), 
                                        @retVal int out)
                                    AS
                                    BEGIN

                                    INSERT INTO MemberProfiles(
                                        EmailAddress, 
                                        UserName, 
                                        ContactNumber, 
                                        [Status]) 
                                        VALUES(@emailAddress, @userName, @contactNumber, @Status);

                                        ---check for number of rows affected
                                        IF(@@ROWCOUNT > 0)
                                        BEGIN
                                            SET @retVal = 200 -- command was executed successfully
                                        END
                                        ELSE
                                        BEGIN
                                            SET @retVal = 500  -- nothing is updated
                                        END
                                    END");
            migrationBuilder.Sql(@"CREATE OR ALTER PROCEDURE sp_GetMembers
                                    AS
                                    BEGIN
                                        Select * FROM MemberProfiles
                                    END");

        }

        protected override void Down(MigrationBuilder migrationBuilder)
        {
               migrationBuilder.Sql("drop procedure sp_createMember");
               migrationBuilder.Sql("drop procedure sp_GetMembers");
        }
    }
}

III. Update-Database

In this part, we will perform a database update. This command will run the migration we created above. To summarize this steps will create the following.

Snippet:

dotnet ef database update

This is how my Database will look like after database update command.

IV. How to Use Stored Procedure in Entity Framework Core

Now that we have created the database and stored procedure, the last thing that we need to do is to call the Stored Procedure that we started a while ago. We will follow the repository pattern concept. We will create an interface and a class. The interface contains the methods and properties, while the class will handle the actual process.

1. Create a service folder and create two files. An Interface which I name IMemberRepository and a class file MemberRepository.

2. Declare a method on IMemberRepository.

• CreateMemberAsync » Insert new member to the database.
• GetAlleMemberAsync » Return all members record

using System.Collections.Generic;
using System.Threading.Tasks;
using SPCoreApi.Models;

namespace SPCoreApi.Repositories
{
    public interface IMemberRepository
    {
          Task<int> CreateMemberAsync(MemberProfile member);
          Task<List<MemberProfile>> GetAlleMemberAsync();

    }
}

3. Open MemberRepository , this class will call the stored procedure. Use the code snippet below to extend your class to the Interface IMemberRepository.

public class MemberRepository : IMemberRepository

Now, you need to initialize this class to interface relationship. To do that open your startup.cs then copy and paste the code snippet below inside ConfigureService method.

services.AddScoped<IMemberRepository, MemberRepository>();

Now, we are all set and ready to create the method inside MemberRepository. EntityFrameworkCore has two methods for executing Stored Procedures.

• FromSqlRaw()
• ExecuteSqlRaw() 
• ExecuteSqlRawAsync()

The snippet below is the code inside MemberRepository class. This class contains two methods CreateMemberAsync and GetAlleMemberAsync. I used FromSqlRaw() in this tutorial.

using System.Collections.Generic;
using System.Data;
using System.Threading.Tasks;
using Microsoft.Data.SqlClient;
using Microsoft.EntityFrameworkCore;
using SPCoreApi.Models;

namespace SPCoreApi.Repositories
{
    public class MemberRepository : IMemberRepository
    {
        private readonly AppDBContext _context;
        public MemberRepository(AppDBContext context)
        {
            _context = context;

        }
        public async Task<int> CreateMemberAsync(MemberProfile member)
        {
         

         var parameter = new List<SqlParameter>();
         parameter.Add(new SqlParameter("@emailAddress", member.EmailAddress));
         parameter.Add(new SqlParameter("@userName", member.EmailAddress));
         parameter.Add(new SqlParameter("@contactNumber", member.EmailAddress));
         parameter.Add(new SqlParameter("@Status", member.EmailAddress));
         parameter.Add(new SqlParameter{
             ParameterName = "@retVal",
                DbType = DbType.Int32,
                Direction = ParameterDirection.Output
         });

            
          var result = await Task.Run(()=>_context.MemberProfiles.FromSqlRaw(@"exec sp_CreateMember 
                                                            @emailAddress,
                                                            @userName,
                                                            @contactNumber,
                                                            @Status,
                                                            @retVal OUT",
                                                            parameter.ToArray()
                                                            ));

           int retVal = int.Parse(parameter[4].Value.ToString()); //get @retVal return value
           return retVal;
        }

        public async Task<List<MemberProfile>> GetAlleMemberAsync()
        {
            List<MemberProfile> members = new List<MemberProfile>();
            var result = await _context.MemberProfiles.FromSqlRaw(@"exec sp_GetMembers").ToListAsync();
             
              foreach(var row in result)
              {
                  members.Add(new MemberProfile{
                      Id = row.Id,
                      EmailAddress = row.EmailAddress,
                      UserName = row.UserName,
                      ContactNumber = row.ContactNumber,
                      Status = row.Status
                  });
              }
            return members;
        }

    }
}

V. Create API Controller

To consume the Repository that we created awhile ago. We need an API controller to return the data. Below is the snippet for the API controller. I named my controller SpReaderController.

using System.Threading.Tasks;
using Microsoft.AspNetCore.Mvc;
using SPCoreApi.Models;
using SPCoreApi.Repositories;

namespace SPCoreApi.Controllers
{
    [ApiController]
    [Route("api/[controller]")]
    public class SpReaderController : ControllerBase
    {
        private readonly IMemberRepository _repository;
        public SpReaderController(IMemberRepository repository)
        {
            _repository = repository;

        }
       [HttpGet]
      public async Task<ActionResult<MemberProfile>> GetAllMember()
      {
          var result = await _repository.GetAlleMemberAsync();
          return Ok(result);
      }

      [HttpPost("CreateMember")]
      public async Task<ActionResult<int>> CreateMember(MemberProfile member)
      {
          var result = await _repository.CreateMemberAsync(member);
          return  Ok(result);
      }
    }
}

To access this method using postman. You can use the following URL.

Here is screenshot of a Postman Request for CreateMember method.

Summary

In this tutorial, we set up the ASP.NET Core Web API project. Configure Entity Framework and created migration for Stored Procedures. We also use the Repository pattern concept. This is how we can use a Stored Procedure in Entity Framework Core. I will also attach a source code to understand what you can do with stored procedures thoroughly.

You can check the GitHub Source code here.