Call Stored Procedure from Entity Framework

Microsoft have introduced entity framework to connect to the database. When you work with the database, you have to call your stored procedure. This blog explain you how to execute your stored procedure from Entity Framework.

Assume that your stored procedure insert/update/delete record into database. It returns nothing just do as per the parameter value.

Follow the below steps:
1. Open Entity Framework Model page in Visual Studio IDE
2. Right click on blank area and choose “Add” -> “Function Import” option
3. In Add Function Import dialog, select your stored procedure, give function name and choose return type
4. Click on Ok button to add the function in Entity Framework
5. In your page, Create instance of entities class and call your function with proper parameter value

If your stored procedure is return multiple record then you have to create one custom entity, which have the same schema as your stored procedure return the data. To create the custom entity, right click on Entity Framework Model page and choose “Add” -> “Entity…” It will open “Add Entity” dialog box where you have to give Entity Information.

You can add/edit/delete properties into custom activity. To add, Select “Scalar Properties” section under custom activity, right click and choose “Add” -> “Scalar Property”.

Just create custom entity. After creating custom entity, Add Function Import to your entity framework model.

Follow the below steps:
1. Open Entity Framework Model page in Visual Studio IDE
2. Right click on blank area and choose “Add” -> “Function Import” option
3. In Add Function Import dialog, select your stored procedure, give function name and choose return type as Entities and select your custom entity from dropdown list.
4. Click on Ok button to add the function in Entity Framework
5. In your page, Create instance of entities class and call your function with proper parameter value


TestDBEntities1 testdb = new TestDBEntities1();
var p = from d in testdb.GetOrderInfo() select d;

Entity Framework Stored Procedure mapping

Introduction

Now days, Microsoft have changed lots of thing in accessing database from .Net application. Microsoft has introduced Entity Framework to access database from the application. Entity framework is just a small wizard which helps to create classes to access the database. To know basic of Entity Framework please click on here.

In Part 1, I explained you how to create entity framework class in your project and how to insert, update or delete the record. Now in part 2, I explain you how to map your stored procedure with insert, update and delete functionality.

If you will not map your stored procedure then Entity Framework will create concrete SQL statement for insert, update and delete. If you will map your stored procedure then Entity Framework will execute your stored procedure with parameter value.

Let take an example of contact table which I explained schema in part 1.

After creating entity framework file (.edmx), Select “Contact” entity and open “Mapping Details” window. In Mapping Details window, click on “Map Entity to Function” button on left side. Refer below screen shot:




In Insert/Update/Delete function, Select your stored procedure and map your parameter value. Refer image below:



Now you have to just add/update/remove data using Entity Framework and It will execute your mapped stored procedure.

Following code is used to add new record into Contact table. When user will execute the code below, Entity Framework will execute “SPE_ContactInsert” stored procedure to insert contact information.

TestEntityEntities testEntityEntities = new TestEntityEntities();
Contact contact = new Contact();
contact.FirstName = "test1";
contact.LastName = "test2";
contact.Title = "Mr.";
testEntityEntities.AddToContact(contact);
testEntityEntities.SaveChanges();

As the same way you can update or delete the record. For more information please refer Entity Framework part 1 here.