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

  SELECT * from Orders
  WHERE Id = @ID

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.

ef core call stored procedure

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)
            //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


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?