Code First Stored Procedure Mapping

• You can use stored procedures either to get the data or to add/update/delete the records to one or multiple database tables.
• Stored procedures and user-defined functions in the database are represented as functions in entity framework.
• The following code will create three procedures Student_Insert, Student_Update and Student_Delete.
• Student_Insert and Student_Update stored procedures have a parameter name which corresponds to the property names.
• Student_Delete will have a primary key property StudentID parameter.

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

• You can also change stored procedure and parameters name:

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.Student_ID, "Student_ID"))
		.Update(sp => sp.HasName("sp_UpdateStudent")
			.Parameter(pm => pm.StudentName, "name"))
		.Delete(sp => sp.HasName("sp_DeleteStudent")
			.Parameter(pm => pm.Student_ID, "Id"))
	);
}

• If you want all your entities to use stored procedures then do the following:

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

• While the above can be used for insert, delete, and update sprocs, using query stored procedures is easier.
  • This can be done using the SqlQuery() method.
  • The results of the stored procedure will be mapped to the appropriate fields and a collection of strongly typed entities are returned.
• Ways to do run the same query using stored procedures:

var parameters = new object[1];
context.Ninjas.SqlQuery("GetNinjasByX", parameters);
context.Database.SqlQuery<Ninja>("GetNinjasByX", parameters);
context.Database.SqlQuery(typeof(Ninja), "GetNinjasByX", parameters);

Limitations

• Only Fluent API can be used to map stored procedures.
  • You cannot use Data Annotation attributes in EF 6 for stored procedure mapping.
• You cannot use a mixture of stored procedure and query to add, update and delete operation on the same entity.
  • You can either use stored procedure or SQL query for all add, update and delete operation with the entity.