How to execute Stored Procedure in Entity Framework Core

Entity Framework 6.x supports stored procedure mapping for CRUD operations. However, this is no longer available in Entity Framework Core. But there are still workaround exists in EF Core to execute stored procedures to get the data and for CRUD operations. In this post, we will see how to execute Stored Procedure in Entity Framework Core using the FromSql method to get the data and ExecuteSqlCommand method for CRUD operations.

How to execute Stored Procedure in Entity Framework Core

For demonstration purpose, I am using a database with only 2 tables and it has following data.

Following is a stored procedure, which returns all the categories.

CREATE PROCEDURE usp_GetAllCategories
AS
BEGIN
   SET NOCOUNT ON;
   SELECT * FROM Categories
END
GO

To execute the stored procedures, use FromSql method which executes RAW SQL queries. But there are certain limitations to execute RAW SQL queries or stored procedures.

• SQL queries can only be used to return entity types that are part of your model. Therefore, it cannot contain related data.
• The SQL query must return data for all the properties of the entity. So basically your SQL query should be Select * from {tableName}
• The column names in the result set must match the column names that properties are mapped to.

Using FromSql, we can execute stored procedure via following way.

List<Category> lst = dataContext.Categories
                    .FromSql("usp_GetAllCategories").ToList();

All good. But the above stored procedure doesn’t take any parameters. So there exists a stored procedure with parameters like,

CREATE PROCEDURE usp_GetProductsByCategory
 @CategoryID int
AS
BEGIN
   SET NOCOUNT ON;
   SELECT * FROM Products where CategoryID = @CategoryID
END
GO

So for parameterized procedures you can use the FromSql() overload, which accepts object[] parameters. Like,

int nCatID = 1;
List<Product> lst = dataContext.Products
                   .FromSql("usp_GetProductsByCategory @p0", nCatID)
                   .ToList();

One thing to notice is the parameter name. It is “@p0”. If more parameters, then increment the counter like @p1, @p2 etc.. Following code shows how to pass multiple parameters.

int nCatID = 1;
var catName = "Clothing";
lstProducts = dataContext.Products
             .FromSql("usp_GetProductsByCategoryIDAndName @p0, @p1", 
                    parameters: new[] { nCatID.ToString(), catName })
              .ToList();

There also exists another way to pass the parameters. Replace @p0 with {0}.

int nCatID = 1;
List<Product> lst = dataContext.Products
                   .FromSql("usp_GetProductsByCategory {0}", nCatID)
                   .ToList();

You can also pass parameters by name. For that, you need to construct a DbParameter and pass it as a parameter value.

int nCatID = 1;
var catParam = new SqlParameter("@CategoryID", nCatID);
List<Product> lst = dataContext.Products
            .FromSql("usp_GetProductsByCategory @CategoryID", catParam)
            .ToList();

FromSql can only be used to execute raw SQL queries or stored procedures to get the data. You can’t use it for INSERT/UPDATE/DELETE. if you want to execute INSERT, UPDATE, DELETE queries, use the ExecuteSqlCommand. It returns integer value which indicates the count of affected rows. Let’s see with an example. Following stored procedure, insert record in Categories table.

CREATE PROCEDURE usp_InsertCategory
 @CategoryName Varchar(300)
AS
BEGIN
    SET NOCOUNT ON;
    Insert into Categories Values (@CategoryName)
END
GO

ExecuteSqlCommand should be used on context.Database. You can use it in the following way.

var catName = "Personal Care";
dataContext.Database
           .ExecuteSqlCommand("usp_InsertCategory @p0", catName);

That’s it.

Thank you for reading. Keep visiting this blog and share this in your network. Please put your thoughts and feedback in the comments section.

PS: If you found this content valuable and want to return the favour, then