Insert, Update and Delete Function Mapping to Stored Procedures In Entity Framework 6

Introduction

It is a known fact that stored procedures are always better when compared with standalone queries whenever performance is considered. So, here we will use the ADO.NET Entity Data Model Designer to map the insert, update, and delete operations of an entity type to stored procedures. As we know very well, the default behaviour of each entity type EDM on insert, update and delete operations is executing auto generated queries.

Observation

Let us assume that we have a single table Department in our database MyOrg and we will add it to our EDM MyOrg.edmx in a console application named ModificationFunctions.

The data in the table is:

Now let us perform Insert, Update and Delete operations and observe the default behaviour.

namespace ModificationFunctions
{
    class Program
    {
        static void Main(string[] args)
        {
            using (MyOrgEntities OE = new MyOrgEntities())
            {
                //Insert Operation
                OE.Departments.Add(new Department() { DName = "Production", HOD = "Zing", Gender = "M" });
                OE.SaveChanges();

                //Update Opertation
                var dept = OE.Departments.Where(x => x.Did == 1003).FirstOrDefault();
                dept.HOD = "Martin";
                OE.SaveChanges();

                //Delete Operation
                var dept1 = OE.Departments.Where(x => x.Did == 1013).FirstOrDefault();
                OE.Departments.Remove(dept1);
                OE.SaveChanges();
            }
        }
    }
}

To observe the default behavior, I will use IntelliTrace Window.

Auto generated insert query:

Auto generated update query:

Auto generated delete query:

Implementation

Step 1

Now let us try to change this default behaviour by replacing all these queries with user defined stored procedures, i.e.,

Create Proc InsertDepartment
(@DName as Varchar(50), @HOD as varchar(50), @Gender as Varchar(50))
as
INSERT INTO Department (DName,HOD,Gender) VALUES (@DName,@HOD,@Gender)

Create Proc UpdateDepartment
(@DName as Varchar(50), @HOD as varchar(50), @Gender as Varchar(50),@Did as int)
as
Update Department Set DName=@DName,HOD=@HOD,Gender=@Gender where Did=@Did

Create Proc DeleteDepartment
(@Did as int)
as
Delete from Department where Did=@Did

Step 2

Now go to MyOrg.edmx designer surface, right-click and select update model from database and then select all these 3 stored procedures from Stored Procedures and Functions and make sure that you uncheck the option Import selected stored procedures and functions into the entity model and then click Finish button. As shown below:

Step 3

Now right-click the entity type Department to map the insert, update and delete operations and select Stored Procedures Mapping. The Map Entity to Functions view of the Mapping Details window appears as shown below:

Step 4

From the drop-down list, select the stored procedure to which the insert, update and delete operation is to be mapped. The window is populated with default mappings between entity properties and stored procedure parameters as shown below and save .edmx file.

Now let us examine the same insert, update and delete operations using IntelliTrace Window and now it should use the imported stored procedures for insert, update and delete operations instead of default queries.

Insert

Update

Delete

Thanks for reading!