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.