CRUD Operation using Stored Procedure in Entity Framework:

In the previous chapter, we have seen how to read data using a stored procedure. In this chapter, we will use stored procedures for CUD (create, update, delete) operation for an entity when we call SaveChanges() method using database-first approach.

We will use the following stored procedures for Student entity:

  1. sp_InsertStudentInfo: Inserts a new student record into the database
  2. sp_UpdateStudent: Updates a student record
  3. sp_DeleteStudent: Deletes a student record in the database.

The followings are SQL scripts for each stored procedure.

Sp_InsertStudentInfo:

CREATE PROCEDURE [dbo].[sp_InsertStudentInfo]
    -- Add the parameters for the stored procedure here
    @StandardId int = null,
    @StudentName varchar(50)
AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;

        INSERT INTO [SchoolDB].[dbo].[Student]([StudentName],[StandardId])
        VALUES(@StudentName, @StandardId)

    SELECT SCOPE_IDENTITY() AS StudentId

END

sp_UpdateStudent:

CREATE PROCEDURE [dbo].[sp_UpdateStudent]
    -- Add the parameters for the stored procedure here
    @StudentId int,
    @StandardId int = null,
    @StudentName varchar(50)
AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;

    Update [SchoolDB].[dbo].[Student] 
    set StudentName = @StudentName,StandardId = @StandardId
    where StudentID = @StudentId;

END

sp_DeleteStudent

CREATE PROCEDURE [dbo].[sp_DeleteStudent]
    -- Add the parameters for the stored procedure here
    @StudentId int
AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;

    DELETE FROM [dbo].[Student]
    where StudentID = @StudentId

END
    

First of all, you need to update your existing EDM to add these stored procedures into your EDM. Right click on your designer and click Update Model from Database.. to open update wizard. Expand Stored Procedures and Functions node and select the above stored procedures and uncheck Import selected stored procedures and function into the entity model checkbox because we will map these procedures with the Student entity directly.

Entity Framework stored procedure

Click on Finish button. The Model Browser will display procedures into Storage model section, but not in Function Imports, as shown below.

Entity Framework stored procedure

You need to map these stored procedures with the Student entity. In the EDM designer, right click on the Student entity and select Stored Procedure Mapping to open Mapping details, as shown below.

Entity Framework stored procedure

In the Mapping Details as shown below, you will see <Select Insert Function>, <Select Update Function>, and <Select Delete Function>. Select the appropriate stored procedure for each one in the dropdown, e.g. sp_InsertStudentInfo for Insert function, sp_UpdateStudent for update function, and sp_DeleteStudent for delete function.

Entity Framework stored procedure

The sp_InsertStudentInfo returns the value of StudentId, map that with Student entity's StudentID, as shown below:

Entity Framework stored procedure

Complete the mapping of Insert, Update and Delete procedures parameter with the appropriate properties of Student entities, as shown below.

Entity Framework stored procedure

Now, we need to validate it before executing to ensure that there will not be a run time error. To accomplish this, right click on the Student entity in the designer and click Validate and make sure that there are no warnings or errors.

Entity Framework stored procedure

Now, whenever you add, update, and delete Student entity, EF will use these stored procedures for CUD operation instead of executing SQL commands. The following example demonstrates this:

using (var context = new SchoolDBEntities())
{
    Student student = new Student() { StudentName = "New student using SP"};

    context.Students.Add(student);
    //will execute sp_InsertStudentInfo 
    context.SaveChanges();

    student.StudentName = "Edit student using SP";
    //will execute sp_UpdateStudent
    context.SaveChanges();

    context.Students.Remove(student);
    //will execute sp_DeleteStudentInfo 
    context.SaveChanges();
}

The above example will execute the following statements on each SaveChanges() call:

exec [dbo].[sp_InsertStudentInfo] @StandardId=NULL,@StudentName='New student using SP'
go

exec [dbo].[sp_UpdateStudent] @StudentId=47,@StandardId=NULL,@StudentName='Edit student using SP'
go

exec [dbo].[sp_DeleteStudent] @StudentId=47
go

Note: Once it executes SaveChanges method after adding a new student, it will assign a database generated value to StudentID property. This is necessary in order to track it and perform further actions on that entity object. The following image shows the value in the debug view of Visual Studio.

Entity Framework stored procedure