Run or Execute Stored Procedure using EF Core

If you are asp.net core developer and working with some stored procedures in database, you would like to know how you can execute or run/call stored procedure in asp.net core using Entity framework(EF) core, so in this article, I have mentioned how to call stored procedure using EF Core with an example.

Suppose this is our sample stored procedure

CREATE PROCEDURE GetCountryByID
(@ID INT)
AS
BEGIN
SET NOCOUNT ON
  SELECT * from Orders
  WHERE Id = @ID
END

As you can understand from stored procedure query, we are simply passing "ID" and selecting a data from table named "Order" based on ID.

Here is how our database table looks like with data.

Now, I have already created ASP.NET Core (Model-View-Controller) project in Visual Studio 2022 and installed below Nuget packages to connect with SQL Server database

Install-Package Microsoft.EntityFrameworkCore.SqlServer
Install-package Microsoft.EntityFrameworkCore.Tools

Once, I have installed above packages, I have added Windows connection string in ASP.NET Core appsettings.JSON

 "ConnectionStrings": {
    "DefaultConnection": "Server=DESKTOP-DOG5T0Q\\SQLEXPRESS;Database=OrderDetails;Trusted_Connection=True;MultipleActiveResultSets=true"
  }

and MyDBContext.cs

using Microsoft.EntityFrameworkCore;

namespace EFCoreStoredProcedure.Models
{
        public class MyDbContext : DbContext
        {
            public MyDbContext(DbContextOptions<MyDbContext> options)
                : base(options)
            { }
            public DbSet<Orders> Orders { get; set; }
        }
}

Then created Orders.cs

namespace EFCoreStoredProcedure.Models
{
    public class Orders
    {
        public int Id { get; set; }
        public string? Country { get; set; }
        public double? Price { get; set; }
        public int? Quantity { get; set; }

    }
}

Now, we will need to navigate to "Program.cs" (as I have using .NET Core 6, below versions had Startup.cs) and use below code

using Microsoft.EntityFrameworkCore;
using EFCoreStoredProcedure.Models;

var builder = WebApplication.CreateBuilder(args);

//some code

//connect to database
builder.Services.AddDbContext<MyDbContext>(options => options.UseSqlServer(builder.Configuration.GetConnectionString("DefaultConnection")));

We are almost there, navigate to "HomeController.cs" and use the below code to call stored procedure in ASP.NET Core using EF Core.

using EFCoreStoredProcedure.Models;
using Microsoft.AspNetCore.Mvc;
using Microsoft.Data.SqlClient;
using Microsoft.EntityFrameworkCore;
using System.Data;
using System.Data.Common;
using System.Diagnostics;

namespace EFCoreStoredProcedure.Controllers
{
    public class HomeController : Controller
    {
        private readonly ILogger<HomeController> _logger;
        private readonly MyDbContext _context;

        public HomeController(ILogger<HomeController> logger, MyDbContext dbContext)
        {
            _logger = logger;
            _context = dbContext;
        }

        public IActionResult Index()
        {
            List<Orders> orders = new List<Orders>();
            DbCommand cmd = _context.Database.GetDbConnection().CreateCommand();

            cmd.CommandText = "dbo.GetCountryByID";
            cmd.CommandType = CommandType.StoredProcedure;

            cmd.Parameters.Add(new SqlParameter("@id", SqlDbType.Int) { Value = 1 });

            //for output params
            //cmd.Parameters.Add(new SqlParameter("@id", SqlDbType.BigInt) { Direction = ParameterDirection.Output });

            if (cmd.Connection.State != ConnectionState.Open)
            {
                cmd.Connection.Open();
            }
            //get result
            var output = cmd.ExecuteReader();

            //loop through results if list
            while (output.Read())
            {
                //add orders in list
                orders.Add(new Orders
                {
                    Id = output.GetInt32(0),
                    Country = output.GetString(1),
                    Price = output.GetDouble(2),
                    Quantity = output.GetInt32(3)
                });
            }

            return View(orders);
        }
    }
}

I have explained above code in comments, we are simply connecting to database first and creating Command

In CommandText we are passing stored procedure name.

In Type, we are mentioning that CommandType = StoredProcedure.

Using Parameters.Add() property, we are passing "ID" as SQL parameter.

Since there is no output parameter in this SP, I have commented out the code, but you can use it to get the output parameter.

Once connection is opened, we are calling cmd.ExecuteReader to output.

And in Index.cshtml, we will use below Razor Code to show output

@model IEnumerable<EFCoreStoredProcedure.Models.Orders>
@{
    ViewData["Title"] = "Home Page";
}

@foreach(var order in Model)
{
    <div>Order Id: @order.Id</div><br/>
    <div>Country: @order.Country</div><br/>
    <div>Price: @order.Price</div><br/>
    <div>Quantity: @order.Quantity</div><br/>
}

Once, you will execute the above code, you will see the output as below

That's it, hope it helps.

You may also like to read:

Bulk Insert in ASP.NET Core MVC using Entity Framework Core

Creating GridView in ASP.NET Core MVC with Paging

Solving Error "JsonException: A possible object cycle was detected" .NET Core

C# GUID

More than one DBContext was found - EF Core

How to get Currently logged in UserId in ASP.NET Core?

How to define C# multiline string literal?