How to call stored procedure in Entity Framework Core with parameters

In this post, we will discuss how to call a stored procedure in Entity Framework Core with parameters from a controller named "Product". Our model includes fields such as Id, ProductName, Description, Price, and LastChanged. Assuming the stored procedure accepts one parameter (Id) and returns product details based on that, you can follow these steps:

Define your stored procedure in your database.

Create a corresponding model or entity in your Entity Framework Core application that represents the result set of the stored procedure. Let's call it "Product".

Here's an example of a stored procedure named "GetProductById" that you can create in your SQL Server database:

CREATE PROCEDURE GetProductById
    @ProductId INT
AS
BEGIN
    SELECT Id, ProductName, Description, Price, LastChanged
    FROM Products
    WHERE Id = @ProductId;
END

This stored procedure takes one parameter @ProductId and returns the product details (Id, ProductName, Description, Price, and LastChanged) for the product with the specified Id from the "Products" table. Adjust the table and column names as per your database schema.

using System.Threading.Tasks;
using Microsoft.Data.SqlClient;
using Microsoft.EntityFrameworkCore;

public class ProductDbContext : DbContext
{
    public ProductDbContext(DbContextOptions<ProductDbContext> options) : base(options)
    {
    }

    public DbSet<Product> Products { get; set; }

    public async Task<Product> GetProductByIdAsync(int productId)
    {
        var productIdParam = new SqlParameter("@ProductId", productId);

        return await Products.FromSqlRaw("EXEC GetProductById @ProductId", productIdParam).FirstOrDefaultAsync();
    }
}

Code example, Create a controller named "ProductController" and inject your DbContext.

using Microsoft.AspNetCore.Mvc;
using System.Threading.Tasks;

[Route("api/[controller]")]
[ApiController]
public class ProductController : ControllerBase
{
    private readonly ProductDbContext _dbContext;

    public ProductController(ProductDbContext dbContext)
    {
        _dbContext = dbContext;
    }

    [HttpGet("{id}")]
    public async Task<ActionResult<Product>> GetProductById(int id)
    {
        var product = await _dbContext.GetProductByIdAsync(id);

        if (product == null)
        {
            return NotFound();
        }

        return product;
    }
}

Ensure that you replace "ProductDbContext", "Product", "GetProductById", and "GetProductByIdAsync" with your actual DbContext class, model name, stored procedure name, and method name, respectively.