Code First Insert Update and Delete Stored Procedure in Entity Framework 6 Pre

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 that for Code First only.

In previous releases of Entity Framework, we can 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 imprortant points

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

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

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

3. 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 propertis (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 first code example.

Configuration of the Connection Sting 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; }

}

Now, create a context class that is derived from the DBContext class.
 

namespace SchoolDataLayer

{ 

 public class SchoolDBContext : DbContext

 {

  public SchoolDBContext(): base("SchoolDBConnectionString")

  {

  }
}
 

In the code above I passed a string in the SchoolDBContext base constructor, that is the name of 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 Procedure in the database.

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