Stored Procedures in Entity Framework Code-First

Back to: Entity Framework Tutorials For Begineers and Professionals

Stored Procedures in Entity Framework Code-First Approach

In this article, I am going to discuss How to Perform CUD Operations using Stored Procedures in Entity Framework Code-First Approach with Examples. Please read our previous article where we discussed How to Generate Context and Entity Classes from an Existing Database using Entity Framework Code-First Approach.

Stored Procedures in Entity Framework Code-First Approach

The Entity Framework Code-First Approach allows us to create and use stored procedures for Insert, Update, and Delete database operations when the SaveChanges() method is called on the context object. Let us understand this with an example. Let us use stored procedures for the CUD (Create/Insert, Update, Delete) operations for the following Student entity.

namespace EFCodeFirstDemo
{
    public class Student
    {
        public int StudentId { get; set; }
        public string FirstName { get; set; }
        public string LastName { get; set; }
    }
}

Then, we need to use the following MapToStoredProcedures() Fluent API method to map an entity with the default stored procedures (these default stored procedures will be created and used by Entity Framework API).

1. MapToStoredProcedures(): This method configures the type to use the default stored procedures for insert, update and delete operations. The default conventions for procedure and parameter names will be used.

So, modify the context class as follows. Here, we are overriding the OnModelCreating method and we are mapping the Student entity with the default stored procedures which are going to be generated by Entity Framework API.

using System.Data.Entity;
namespace EFCodeFirstDemo
{
    public class EFCodeFirstContext : DbContext
    {
        public EFCodeFirstContext() : base("name=MyConnectionString")
        {
            //Setting the Database Initializer as DropCreateDatabaseAlways
            Database.SetInitializer(new DropCreateDatabaseAlways<EFCodeFirstContext>());
        }

        protected override void OnModelCreating(DbModelBuilder modelBuilder)
        {
            //Map Student Entity to Default Stored Procedures
            modelBuilder.Entity<Student>()
                        .MapToStoredProcedures();
        }

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

Please make sure to have the connection string with the name MyConnectionString within the app.config file or web.config file as shown in the below image.

Next, modify the Main method as follows:

using System;
namespace EFCodeFirstDemo
{
    class Program
    {
        static void Main(string[] args)
        {
            using (EFCodeFirstContext context = new EFCodeFirstContext())
            {
                //Write the SQL Script on the Console Window
                context.Database.Log = Console.Write;

                Console.WriteLine("Student Added");
                var student = new Student() { FirstName = "Pranaya", LastName = "Rout"};
                context.Students.Add(student);
                context.SaveChanges();
                
                Console.WriteLine("\nStudent Updated");
                var StudentId1 = context.Students.Find(1);
                StudentId1.FirstName = "Anurag";
                context.SaveChanges();

                Console.WriteLine("\nStudent Deleted");
                var StudentToBeDeleted = context.Students.Find(1);
                context.Students.Remove(StudentToBeDeleted);
                context.SaveChanges();
            }
            Console.ReadKey();
        }
    }
}

With the above changes in place, now run the application and verify the database. You will see that Entity Framework API will create three stored procedures i.e. Student_Insert, Student_Update, and Student_Delete for the Student entity to perform the INSERT, UPDATE, and DELETE Operations as shown in the below image.

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 i.e. StudentId of the Student entity. The following is the generated stored procedures script on the SQL Server database.

-- Student_Insert Stored Procedure
CREATE PROCEDURE [dbo].[Student_Insert]
    @FirstName [nvarchar](max),
    @LastName [nvarchar](max)
AS
BEGIN
    INSERT [dbo].[Students]([FirstName], [LastName])
    VALUES (@FirstName, @LastName)
    
    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
GO

-- Student_Update Stored Procedure
CREATE PROCEDURE [dbo].[Student_Update]
    @StudentId [int],
    @FirstName [nvarchar](max),
    @LastName [nvarchar](max)
AS
BEGIN
    UPDATE [dbo].[Students]
    SET [FirstName] = @FirstName, [LastName] = @LastName
    WHERE ([StudentId] = @StudentId)
END

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

Note: For Adding a Student Entity, it will use Student_Insert Stored Procedure, for Updating Student Entity, it will use Student_Update Stored Procedure and for deleting a Student Entity, it will use Student_Delete Stored Procedure.

How to Map Custom Stored Procedures to an Entity using EF Code First Approach?

The Entity Framework API also allows us to use our own custom stored procedures to perform the Create, Update, and Delete database operations. In this case, we need to explicitly map the custom stored procedure to the entity. We can also configure parameter mapping with entity properties.

Let us understand this with an example. Please modify the context class as follows. Here, we are mapping custom stored procedures with the Student entity. In the below code, we are mapping the Student entity to sp_InsertStudent, sp_UpdateStudent, and sp_DeleteStudent stored procedures. It also configures mapping between parameters and entity properties.

using System.Data.Entity;
namespace EFCodeFirstDemo
{
    public class EFCodeFirstContext : DbContext
    {
        public EFCodeFirstContext() : base("name=MyConnectionString")
        {
            //Setting the Database Initializer as DropCreateDatabaseAlways
            Database.SetInitializer(new DropCreateDatabaseAlways<EFCodeFirstContext>());
        }

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

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

Now run the application and verify the database. You will see that Entity Framework API will create three custom stored procedures i.e. sp_InsertStudent, sp_UpdateStudent, and sp_DeleteStudent for the Student entity to perform the INSERT, UPDATE, and DELETE Operations as shown in the below image.

The following script is generated and executed to create the above custom stored procedures by entity framework.

CREATE PROCEDURE [dbo].[sp_InsertStudent]
    @FirstName [nvarchar](max),
    @LastName [nvarchar](max)
AS
BEGIN
    INSERT [dbo].[Students]([FirstName], [LastName])
    VALUES (@FirstName, @LastName)

    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].[sp_UpdateStudent]
    @StudentId [int],
    @FirstName [nvarchar](max),
    @LastName [nvarchar](max)
AS
BEGIN
    UPDATE [dbo].[Students]
    SET [FirstName] = @FirstName, [LastName] = @LastName
    WHERE ([StudentId] = @StudentId)
END

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

How to Use Stored Procedures for All Entities?

Individually, you can map all entities to their corresponding default stored procedures. But it is also possible to map all entities with the default stored procedures in a single statement. Let us understand this with an example. Let us add the following Standard Entity.

namespace EFCodeFirstDemo
{
    public class Standard
    {
        public int StandardId { get; set; }
        public string StandardName { get; set; }
        public string Description { get; set; }
    }
}

Now, we need to generate default stored procedures for Student and Standard entities. To do so, modify the context class as follows.

using System.Data.Entity;
namespace EFCodeFirstDemo
{
    public class EFCodeFirstContext : DbContext
    {
        public EFCodeFirstContext() : base("name=MyConnectionString")
        {
            //Setting the Database Initializer as DropCreateDatabaseAlways
            Database.SetInitializer(new DropCreateDatabaseAlways<EFCodeFirstContext>());
        }

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

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

With the above changes in place, now run the application and verify the database. You will see that Entity Framework API will create six stored procedures as shown in the below image.

Note: We can only use Fluent API configurations to map stored procedures with EF Code First. We cannot use Data Annotation Attributes for stored procedure mapping.

In the next article, I am going to discuss Cascade Delete in Entity Framework Code First Approach with Examples. Here, in this article, I try to explain How to Perform CUD Operations using Stored Procedures in Entity Framework Code-First Approach with Examples. I hope you enjoyed this CUD Operations using Stored Procedures in Entity Framework Code-First Approach article. Please give your valuable feedback and suggestions about this article.