How to call stored procedure in Entity Framework Core database First

In this post, we will discuss how to call a stored procedure in Entity Framework Core using the database-first approach. We have a model class named Product, which includes fields such as Id, ProductName, Description, Price, and LastChanged. Now, we will create a stored procedure for inserting entries into the database.

Define your stored procedure in your SQL Server database:

CREATE PROCEDURE InsertProduct
    @ProductName NVARCHAR(100),
    @Description NVARCHAR(255),
    @Price DECIMAL(10, 2),
    @LastChanged DATETIME
AS
BEGIN
    INSERT INTO Products (ProductName, Description, Price, LastChanged)
    VALUES (@ProductName, @Description, @Price, @LastChanged);
END

This stored procedure takes parameters for the product's name, description, price, and last changed date, and inserts a new record into the "Products" table.

In your DbContext class, create a method to execute the stored procedure:

using Microsoft.EntityFrameworkCore;

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

    public void InsertProduct(string productName, string description, decimal price, DateTime lastChanged)
    {
        Database.ExecuteSqlRaw("EXEC InsertProduct @ProductName, @Description, @Price, @LastChanged",
            new SqlParameter("@ProductName", productName),
            new SqlParameter("@Description", description),
            new SqlParameter("@Price", price),
            new SqlParameter("@LastChanged", lastChanged));
    }
}

Use this method in your controller to insert a new product:



using Microsoft.AspNetCore.Mvc;
using System;

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

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

    [HttpPost]
    public IActionResult InsertProduct(ProductInputModel model)
    {
        _dbContext.InsertProduct(model.ProductName, model.Description, model.Price, DateTime.Now);

        return Ok("Product inserted successfully");
    }
}

public class ProductInputModel
{
    public string ProductName { get; set; }
    public string Description { get; set; }
    public decimal Price { get; set; }
}
Now, you can make a POST request to your API endpoint /api/product with a JSON payload containing the product details to insert a new product into the database.
Adjust the table and column names, as well as the parameter types and lengths, according to your database schema and requirements.

How to call stored procedure in Entity Framework Core database First

Read Similar Articles