Executing Stored Procedures in EF Core

In the previous post we saw on how to execute SQL Queries from EF Core context, in this post we shall see on how to execute stored procedures using EF Core. Let us use the same Employee table, and create the following stored procedure to get the list of employees.
CREATE PROCEDURE proc_GetEmployees

       @age int = 0

AS

BEGIN

       SET NOCOUNT ON;

       SELECT * FROM dbo.Employee WHERE Age > @age

END

Next we will call the stored procedure from Entity Framework core using the following code.

       var employeeList = _context.Employee

           .FromSql("EXECUTE dbo.proc_GetEmployees")

           .ToList();

We can also pass parameters to Stored Procedures from EF Core, notice that the stored procedure proc_GetEmployees takes an optional parameter age and returns all employees who have age greater than the parameter value. Let us pass this parameter and get the filtered list of employees using in the below code.

        int age = 30;

        var employeeList = _context.Employee

            .FromSql("EXECUTE dbo.proc_GetEmployees {0}", age)

            .ToList();

And we can also use the string interpolation syntax to pass parameter values as follows.

       int age = 30;       
       var employeeList = _context.Employee

            .FromSql($"EXECUTE dbo.proc_GetEmployees {age}")

            .ToList();