Code-First Stored Procedure Entity Framework 6.0

This blog shows configuration of code first making use of stored procedure for the entities. For usage of stored procedure, the OnModelCreating method of DBContext should be overriden,this method will contain code for mapping stored procedure.

protected override void OnModelCreating(DbModelBuilder modelBuilder)  
{  
    modelBuilder.Entity<DefinedEntity>().MapToStoredProcedures();  
}  

The MapToStoreProcedures method configures stored procedures for Insert,Delete and Update. This method has two overloaded methods.

The first overloaded method is parameterless MapToStoredProcedures() which will create three stored procedure for Insert,Delete,Update named as <type_name>_Insert, <type_name>_Update and <type_name>_Delete. 
(For e.g : student_Insert , student_Update, student_Delete)

The second overloaded method is as follows :

MapToStoredProcedures(Action<ManyToManyModificationStoredProceduresConfiguration<TEntityType,TTargetEntityType>>)

The above declared method takes an action as input. The functionality provided by this method is customization of Stored Procedure name, parameter, schema name and so on.

Example to create stored procedure in code first entity framework 6.0

Define a student Model

using System.Web.Mvc;
using System.ComponentModel.DataAnnotations;
using System.ComponentModel.DataAnnotations.Schema;
using System;

namespace Student_Management_System.Models
{
    public class Student
    {
        [Key]
        public int student_id { get; set; }
         
        [Required]
        [Display(Name = "First Name")]
        public string Fname { get; set; }

        [Required]
        [Display(Name = "Last Name")]
        public string Lname { get; set; }

        [Required]
        public string Email { get; set; }
    }
}

Definition of studentcontext class

using System.Data.Entity;
using Student_Management_System.Models;
using System.Diagnostics;

namespace Student_Management_System.DAL
{
    public class studentcontext : DbContext
    {
        public studentcontext() : base("StudentConnString") {
            Database.Log = message => Trace.WriteLine(message);
        }
        public DbSet<Student> students { get; set; }

        protected override void OnModelCreating(DbModelBuilder modelBuilder)
        {
		    
            modelBuilder.Entity<Student>()
                .MapToStoredProcedures(s => s.Insert(u => u.HasName("InsertStudent", "dbo"))  //for Insert InsertStudent stored procedure is created 
                                                .Update(u => u.HasName("UpdateStudent", "dbo"))  //for Update UpdateStudent stored procedure is created 
                                                .Delete(u => u.HasName("DeleteStudent", "dbo"))  //for Delete DeleteStudent stored procedure is created 
                );
        }
    }
}

After creating studentcontext class run the migration steps :

Step 1 : enable-migrations

Step 2 : add-migration 

Initial_SP is the file created in Migrations folder by the add migration command. This file will contain definition for all the Stored Procedures.

namespace Student_Management_System.Migrations
{
    using System;
    using System.Data.Entity.Migrations;
    
    public partial class Initial_SP : DbMigration
    {
        public override void Up()
        {
            CreateStoredProcedure(
                "dbo.InsertStudent",
                p => new
                    {
                        Fname = p.String(),
                        Lname = p.String(),
                        Email = p.String(),
                    },
                body:
                    @"INSERT [dbo].[Students]([Fname], [Lname], [Email])
                      VALUES (@Fname, @Lname, @Email)
                      
                      DECLARE @student_id int
                      SELECT @student_id = [student_id]
                      FROM [dbo].[Students]
                      WHERE @@ROWCOUNT > 0 AND [student_id] = scope_identity()
                      
                      SELECT t0.[student_id]
                      FROM [dbo].[Students] AS t0
                      WHERE @@ROWCOUNT > 0 AND t0.[student_id] = @student_id"
            );
            
            CreateStoredProcedure(
                "dbo.UpdateStudent",
                p => new
                    {
                        student_id = p.Int(),
                        Fname = p.String(),
                        Lname = p.String(),
                        Email = p.String(),
                    },
                body:
                    @"UPDATE [dbo].[Students]
                      SET [Fname] = @Fname, [Lname] = @Lname, [Email] = @Email
                      WHERE ([student_id] = @student_id)"
            );
            
            CreateStoredProcedure(
                "dbo.DeleteStudent",
                p => new
                    {
                        student_id = p.Int(),
                    },
                body:
                    @"DELETE [dbo].[Students]
                      WHERE ([student_id] = @student_id)"
            );
            
        }
        
        public override void Down()
        {
            DropStoredProcedure("dbo.DeleteStudent");
            DropStoredProcedure("dbo.UpdateStudent");
            DropStoredProcedure("dbo.InsertStudent");
        }
    }
}

Step 3 : update-database

This command will be creating stored procedure named "InsertStudent","UpdateStudent","DeleteStudent" in database. The definition of these stored procedure is as follows :-

//For Insert
CREATE PROCEDURE [dbo].[InsertStudent]
    @Fname [nvarchar](max),
    @Lname [nvarchar](max),
    @Email [nvarchar](max)
AS
BEGIN
    INSERT [dbo].[Students]([Fname], [Lname], [Email])
    VALUES (@Fname, @Lname, @Email)
    
    DECLARE @student_id int
    SELECT @student_id = [student_id]
    FROM [dbo].[Students]
    WHERE @@ROWCOUNT > 0 AND [student_id] = scope_identity()
    
    SELECT t0.[student_id]
    FROM [dbo].[Students] AS t0
    WHERE @@ROWCOUNT > 0 AND t0.[student_id] = @student_id
END


//For Update

CREATE PROCEDURE [dbo].[UpdateStudent]
    @student_id [int],
    @Fname [nvarchar](max),
    @Lname [nvarchar](max),
    @Email [nvarchar](max)
AS
BEGIN
    UPDATE [dbo].[Students]
    SET [Fname] = @Fname, [Lname] = @Lname, [Email] = @Email
    WHERE ([student_id] = @student_id)
END

//For Delete

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

Now these stored procedure can be used in controller action :-

using System.Net;
using System.Web;
using System.Web.Mvc;
using Student_Management_System.DAL;
using Student_Management_System.Models;

namespace Student_Management_System.Controllers
{
    public class StudentsController : Controller
    {
        private studentcontext db = new studentcontext();

        Student stu = new Student();
      
        public ActionResult Index()
        {
            stu.Fname = "suraj";
            stu.Lname = "rana";
            stu.Email = "s@gmail.com";

            db.students.Add(stu);   //For add internally "InsertStudent" stored procedure is called
            db.SaveChanges();

            return View(db.students.ToList());
        }
     }
}

The output that for add command internally "InsertStudent" stored procedure is called can be logged by using following code within studentcontext.

public studentcontext() : base("StudentConnString") {
            Database.Log = message => Trace.WriteLine(message);
        }

The following is the SQL logging output of the code above: