chevron_left
399 points
8 4 3

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

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). 

Example

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_InsertStudent_Update and Student_Delete for the above Student entity, as shown below.

code first stored procedures

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. 

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_InsertStudentsp_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.

More Posts

How to test stored procedure in sql server amna - Jul 4
Where are phone numbers stored on android amna - Jul 3
Where are google drive offline files stored amna - Jun 26
Name validation in javascript using regular expression Sanjana Sagar - Jul 8
How to find length of string in c++ using strlen Sanjana Sagar - May 22
Prevent image layout shifts in Sanity + Gatsby dev - Aug 9
What is Namespace std in C++ sakshi - Jul 11
Stored procedures execute faster than an equivalent sql script because stored procedures are what? sakshi - Sep 4
Basic mathematical operations on Arrays in java using stream(). DivyaN - Aug 5
C++ program accept five integer from user that will be stored in an array using pointer and print them in reverse order. Ahsan29 - May 7