How to use stored procedure in Entity Framework Core

First, ensure that you have a stored procedure created in your database.

For example:

CREATE PROCEDURE sp_GetProductsByCategoryId
    @CategoryId int
AS
BEGIN
    SELECT * FROM Products WHERE CategoryId = @CategoryId
END

If your stored procedure returns a specific result set, create a model that matches the structure of that result.

For example:

public class Product
{
    public int Id { get; set; }
    public string Name { get; set; }
    public decimal Price { get; set; }
}

You'll need to add a method in your DbContext class to call the stored procedure.

For example:

public class AppDbContext : DbContext
{
    public DbSet<Product> Products { get; set; }

    public AppDbContext(DbContextOptions<AppDbContext> options) : base(options) { }

    public async Task<List<Product>> GetProductsByCategoryIdAsync(int categoryId)
    {
        var categoryIdParam = new SqlParameter("@CategoryId", categoryId);
        return await Products.FromSqlRaw("EXEC GetProductsByCategory @CategoryId", categoryIdParam).ToListAsync();
    }
}

Here is a simple example that shows how to use stored procedure with output value in Entity Framework Core

var params = new[] {
    new Microsoft.Data.SqlClient.SqlParameter("@year", 2024),
    new Microsoft.Data.SqlClient.SqlParameter()
    {
        ParameterName = "@total",
        SqlDbType = System.Data.SqlDbType.Int,
        Direction = System.Data.ParameterDirection.Output
    },
};
 
var db = new MyDbContext();
int n = db.Database.ExecuteSqlRaw("EXEC sp_GetTotal @year, @total out", params);
int total = Convert.ToInt32(params[1].Value);  // @total output value

The stored procedure sp_GetTotal accepts one input parameter @year and one output parameter @total. To pass these parameters, you can create an array of SqlParameter instances. Alternatively, you can pass them individually as the second and third parameters of the ExecuteSqlRaw() method.

To call a stored procedure in EF Core, use Database.ExecuteSqlRaw() or Database.ExecuteSqlInterpolated(), as Database.ExecuteSqlCommand() is deprecated. It's important to note that the SqlParameter class is in the Microsoft.Data.SqlClient namespace, not System.Data.SqlClient. Using the latter may result in a confusing error, so make sure to use new Microsoft.Data.SqlClient.SqlParameter(...) when creating parameters.