Entity Framework Core Stored Procedure
Introduction
The Entity Framework allows you to use stored procedures to perform predefined logic on database tables. Raw SQL queries can be used to execute a stored procedure.
Here is a simple stored procedure, it will insert a customer record into a Customers table when executed.
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[CreateCustomer]') AND type in (N'P', N'PC')) BEGIN EXEC dbo.sp_executesql @statement = N' CREATE PROCEDURE [dbo].[CreateCustomer] @FirstName Varchar(50), @LastName Varchar(50), @Address Varchar(100) AS INSERT INTO dbo.Customers( [FirstName], [LastName], [Address] ) VALUES (@FirstName, @LastName,@Address) ' END GO
The ExecuteSqlCommand()
method can be used to execute database commands as a string, and it will return the number of affected rows.
using (var context = new MyContext()) { int affectedRows = context.Database.ExecuteSqlCommand("CreateCustomer @p0, @p1, @p2", parameters: new[] { "Elizabeth", "Lincoln", "23 Tsawassen Blvd." }); }
Similarly, you can execute stored procedures for Update and Delete commands.