.Net MVC Entity Framework 6 Stored Procedures

ORMs like the Entity Framework 6 are an easy way to handle database operation without using plain SQL. The fundamental issue with this approach is the performance. The combination of EF 6 and .Net MVC can lead to sluggish behavior on the client side, especially when the data model is extremely complex.
1 answer

.Net MVC Entity Framework 6 Stored Procedures

One way to improve the performance without changing the complete design of the database is the use of a Stored Procedure (Sp). Starting with version 6 the Entity Framework supports the Code First approach of SPs for some or all entities.

One way to do this, is to override the OnModelCreating method with the new MapToStoredProcedures() method

The method has two overrides:

- The default method will generate the SPs _Insert, _Update, _Delete

modelBuilder.Entity().MapToStoredProcedures();

- The other override allows programmers to customize the SPs:

modelBuilder.Entity().MapToStoredProcedures(s => s.Update(u = > u.HasName(“modify”).Parameter(b =>b.Value,”entity_value”)));

To access the SPs generated by Code First simple call:

var context = new DatabaseContext();
var param = new SqlParameter("@Attribute", value);
var result = context.Database.SqlQuery("storedProcedure_Name", params);

Taggings: