Executing Stored Procedure in EF Core using RawSQL and FromSqlInterpolated
In this article, we shall see how to use stored Procedure in Entity Framework Core an ORM framework like EF Core.
Stored procedures are SQL statements generally in the form of functions that can take inputs and execute the logic on the server side for the given conditions.
The good thing about EFCore an ORM framework is that it lets you execute any stored procedure easily.
It lets you use a DBContext instance to execute any stored procedure performing CRUD operation on the SQL server instance.
You can use below few methods to execute Stored procedures from your API or ASP.NET Core applications,
- FromSqlRaw– Creates a LINQ query based on an interpolated using plain strings representing a SQL query.
- FromSqlInterpolated – Creates a LINQ query based on a using interpolated string syntax to create parameters string representing a SQL query.
Today in this article, we will cover below aspects,
Let’s start with creating a simple Stored procedure. We will execute it from .NET Core Data access layer.
Writing simple Stored procedure
This Stored procedure returns a list of all employees from the Database.
Example 1
CREATE PROCEDURE GetEmployess
AS
BEGINselect * from Employee
END
Once executed above stored procedure will be listed under the Stored Procedure section.
Executing Stored procedure in EFCore –FromSqlRaw
Below is the code to execute the SP using DBContext and FromSqlRaw,
For the complete example above please refer to articles like Getting Started with EF Core and Repository Implementation of EF Core .
We shall be using the context object to execute the stored procedure as below,
FromSqlRaw Creates a LINQ query based on an interpolated using plain strings representing a SQL query as shown below,
var employees = _context.Employee.FromSqlRaw($"EXEC GetEmployess").ToList();
Stored procedure with Single or Multiple Parameters
Example
Below stored procedures return the list based on matching FirstName
CREATE PROCEDURE GetEmpDetails@FirstName nvarchar(30)
AS
BEGIN
select * from Employee where FirstName = @FirstName
END
Executing the above command creates a procedure.
EXEC GetEmpDetails@FirstName = ‘TheCodeBuzz’
var employees = _context.Employee.FromSqlRaw($"EXEC GetEmpDetails @FirstName = {employeeName}").ToList();
Executing Read or Update Stored procedure in EFCore
Performing Insert or Update using the Stored procedure can be done using the FromSqlInterpolated method as shown below,
_context.Employee.FromSqlInterpolated($"Exec Insert_Employee @FirstName = { emp.FirstName } , @LastName = { emp.LastName },@Address = {emp.Address}, @ZipCode = { emp.ZipCode} ,@State = { emp.State }, @Country = {emp.Country} ,@EmployeeID ={emp.EmployeeId}");
Above I have created the stored procedure Insert_Employee which inserts a new record to the SQL database.
That’s All!
This was very basic we covered today. Hope this helps you get started if you are new to the SQL world.
Please bookmark this page and share it with your friends. Please Subscribe to the blog to receive notifications on freshly published(2024) best practices and guidelines for software design and development.
