How to use stored procedure in Entity Framework Core
By FoxLearn 11/1/2024 2:21:28 PM 50
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.
- Resolving the "Command 'dotnet ef' Not Found" Issue When Creating Migrations
- How to solve 'DbContextOptionsBuilder' does not contain a definition for 'UseSqlServer' in .NET Core
- How to use Identity column in EF7
- How to enable MultipleActiveResultSets
- How to insert master/details data in EF Code First
- Connection String in Entity Framework 6
- How to use decimal precision and scale in EF Code First
- How to enable webRTC in CefSharp in C#