How to use stored procedure in Entity Framework Core
By FoxLearn 11/1/2024 2:21:28 PM 8
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.
- How to use decimal precision and scale in EF Code First
- How to enable webRTC in CefSharp in C#
- How to fix 'CEF can only be initialized once per process'
- How to prevent target blank links from opening in a new window in Cefsharp
- How to allow and manipulate downloads in Cefsharp
- How to add new items to the native Context Menu in CefSharp
- How to disable printing in Cefsharp
- How to disable the SameSite Cookies policy in Cefsharp