CUD Operations using Stored Procedures in Entity Framework 6 Code-First Approach

Entity Framework 6 Code-First provides the ability to create and use a stored procedure for add, update, and delete operations when the SaveChanges() method is called.

Let's use stored procedures for the CUD (Create, Update, Delete) operations for the following Student entity.

class Student
{
    public int StudentId { get; set; }
    public string StudentName { get; set; }
    public DateTime DoB { get; set; }
}

Use the MapToStoredProcedures() method to map an entity with the default stored procedures (these default stored procedures will be created by EF API). The following example maps the Student entity with the default stored procedures.

public class SchoolContext: DbContext 
{
    protected override void OnModelCreating(DbModelBuilder modelBuilder)
    {
        modelBuilder.Entity<Student>()
                    .MapToStoredProcedures();
    }

    public DbSet<Student> Students { get; set; }
}

EF API will create three procedures Student_Insert, Student_Update and Student_Delete for the above Student entity, as shown below.

The Student_Insert and Student_Update stored procedures include parameters for all the properties of the Student entity. The Student_Delete procedure includes a parameter only for the primary key property StudentId of the Student entity. The following is a stored procedures script.

CREATE PROCEDURE [dbo].[Student_Insert]
    @StudentName [nvarchar](max),
    @DoB [datetime]
AS
BEGIN
    INSERT [dbo].[Students]([StudentName], [DoB])
    VALUES (@StudentName, @DoB)
    
    DECLARE @StudentId int
    SELECT @StudentId = [StudentId]
    FROM [dbo].[Students]
    WHERE @@ROWCOUNT > 0 AND [StudentId] = scope_identity()
    
    SELECT t0.[StudentId]
    FROM [dbo].[Students] AS t0
    WHERE @@ROWCOUNT > 0 AND t0.[StudentId] = @StudentId
END

CREATE PROCEDURE [dbo].[Student_Update]
    @StudentId [int],
    @StudentName [nvarchar](max),
    @DoB [datetime]
AS
BEGIN
    UPDATE [dbo].[Students]
    SET [StudentName] = @StudentName, [DoB] = @DoB
    WHERE ([StudentId] = @StudentId)
END

CREATE PROCEDURE [dbo].[Student_Delete]
    @StudentId [int]
AS
BEGIN
    DELETE [dbo].[Students]
    WHERE ([StudentId] = @StudentId)
END

Map Custom Stored Procedures to an Entity

EF 6 allows you to use your own custom stored procedures and map them to an entity. You can also configure parameters mapping with entity properties.

The following example maps custom stored procedures with the Student entity.

protected override void OnModelCreating(DbModelBuilder modelBuilder)
{
    modelBuilder.Entity<Student>()
            .MapToStoredProcedures(p => p.Insert(sp => sp.HasName("sp_InsertStudent").Parameter(pm => pm.StudentName, "name").Result(rs => rs.StudentId, "Id"))
                    .Update(sp => sp.HasName("sp_UpdateStudent").Parameter(pm => pm.StudentName, "name"))
                    .Delete(sp => sp.HasName("sp_DeleteStudent").Parameter(pm => pm.StudentId, "Id"))
            );
}

In the above example, the Student entity is mapped to sp_InsertStudent, sp_UpdateStudent and sp_DeleteStudent stored procedures. It also configures mapping between parameters and entity properties.

Use Stored Procedures for All Entities

You can map all your entities with the default stored procedures in a single statement as shown below.

protected override void OnModelCreating(DbModelBuilder modelBuilder)
{
    modelBuilder.Types().Configure(t => t.MapToStoredProcedures());
}

Limitations

• Only Fluent API can be used to map stored procedures. No data annotation attributes are available in EF 6 for stored procedure mapping.
• You must map insert, update and delete stored procedures to an entity if you want to use stored procedure for CUD operations. Mapping only one of them is not allowed.