This post is on using stored procedure in EF Core. The support for stored procedure in EF Core is similar to the earlier versions of EF Code first. In this post I am using NorthWind database for demo purposes. I am using database first approach to generate model classes. First I have created three stored procedures. One will select all the rows in products table, another with a parameter and the third one is inserting the data to table. Here is the implementation.
You need to create yourDbContext class by inherting the DbContext class from EF. The stored procedures are executing using the DbContext. Here the DbContext class generated using EF migrations.
I am injecting the NorthWindDbContext in controller so that I can use it. And you can execute the “usp_GetAllProducts” procedure like this, which has no parameters, and return the Products entity.
And here is the screenshot of the procedure running on my system.
And for parameterized procedures you can use the FromSql() overload, which accepts object parameters, like this.
One thing you might noticed the parameter name is “@p0”, it is because named parameters support not available in EF Core. FromSql method you can also used for raw SQL command as well. If you’re executing INSERT, UPDATE, DELETE queries, you can use the ExecuteSqlCommand, which is similar to FromSql, but returns only int, the affected rows.
To get the FromSql command, you need to add the reference of “Microsoft.EntityFrameworkCore.Relational” package in your project.json file.