Entity Framework 6 CRUD Stored Procedures: Code-First Approach

Microsoft has announced the new version of Entity Framework 6 Alpha 3 with many new features for models created with Code First or the EF Designer.

So, in this article, we will discuss the new features for Code First only.

In previous releases of Entity Framework, we could only map Code First entities directly to tables, but there was no direct way to map the Stored Procedures from the model created in Code First.

So, in the latest release of Entity Framework 6, Microsoft has provided the new functionality for Code First for mapping to insert, update, and delete Stored Procedures using Fluent API.

Note. Stored Procedure mapping can currently only be done with the Fluent API. You cannot use a mixture of Stored Procedures and direct table access for a given entity.

Some important points

The following are some important points that you need to know about this.

• Naming Convention of Stored Procedures: Three Stored Procedures are created in the given database named <type_name>_Insert, <type_name>_Update, and <type_name>_Delete (for example Student_Insert, Student_Update, and Student_Delete).
• Parameter Names in Stored Procedures: Parameter names will be mapped to the property names in the entity. You can also rename the column for a given property that is used for parameters instead of the default property name.

Number of Parameters in Insert, Update, and Delete Stored Procedure

• The insert Stored Procedure will take a parameter for every property in the model entity, except for auto-generated properties (identity or computed).
• The update Stored Procedure will have a parameter for every property, except for those marked as identity or computed property.
• The delete Stored Procedure should have a parameter for the key value of the entity or can have multiple parameters if the entity has a composite key.
• So, now let's start to Code First Insert/Update/Delete Stored Procedure Mapping using Entity Framework 6 Pre in VS2012.
• First, we need to install the Entity Framework 6 Pre in Visual Studio 2012.

How to Install the Entity Framework 6 Beta version in .NET Framework 4.5

From the Tools menu, select Library Package Manager and then click Package Manager Console.

To install EntityFramework, run the following command in the Package Manager Console.

PM> Install-Package EntityFramework -Pre

Now, Let's create the first code example.

Configuration of the Connection String in the Web Config file.

Create the Class Library project in Visual Studio 2012.

using System;
using System.Collections.Generic;
using System.ComponentModel.DataAnnotations;
using System.Data.Entity;

public partial class Student_Details
{
    [Key]
    public int StudentID { get; set; }
    public string StudentName { get; set; }
    public string Address { get; set; }
    public string Course { get; set; }
}

namespace SchoolDataLayer
{
    public class SchoolDBContext : DbContext
    {
        public SchoolDBContext() : base("SchoolDBConnectionString")
        {
        }

        // DbSet<Student_Details> Student_Details { get; set; } // Add this line if needed
    }
}

In the code above I passed a string in the SchoolDBContext base constructor, which is the name of the connection string I will configure in the earlier step.

Now, override the OnModelCreating method of the DBContext class using Fluent API.

protected override void OnModelCreating(DbModelBuilder modelBuilder)
{
    // Configure domain classes using Fluent API here
    modelBuilder.Entity<Student_Details>().MapToStoredProcedures();
}

The full code is.

using System;
using System.Data.Entity;
using System.Data.Entity.Infrastructure;

public class SchoolDBContext : DbContext
{
    public SchoolDBContext() : base("name=SchoolDBConnectionString")
    {
    }

    protected override void OnModelCreating(DbModelBuilder modelBuilder)
    {
        // Configure domain classes using Fluent API here

        modelBuilder.Entity<Student_Details>().MapToStoredProcedures();
    }

    public DbSet<Student_Details> Student_Detail { get; set; }
}

The MapToStoredProcedures() will build the expected shape of the Stored Procedures in the database.

Now in the Default.aspx.cs page create an object of the model and add it to the Dbset collection.

protected void Page_Load(object sender, EventArgs e)
{
    SchoolDBContext str;
    using (str = new SchoolDBContext())
    {
        str.Student_Detail.Add(new Student_Details() { StudentID = 1, StudentName = "amit", Address = "Noida", Course = "MCA" });
        str.SaveChanges();
    }
}

After you run the SaveChanges() method this will automatically create a database, a table, and 3 Stored Procedures in the database.

You will see your created Stored Procedure in the database for the given connecting string.