ADO.Net Entity Framework call Stored Procedures

In order to select data from a Model using the ADO.Net Entity Data Model, we should first create an ADO.Net Entity Data Model class, refer to the post Creating your First ADO.Net Entity Data Model to see on how to create an ADO.Net Entity Data Model, that maps to the physical database.

Once you have created the ADO.Net Entity Data Model class, we can proceed with querying the model objects. In this post we shall see on how to use the ADO.Net Entity Data Model to call a Stored Procedure and bind the results of the procedure to a GridView.

In this post, we will use an existing Stored procedure and call it using ADO.Net Entity Framework.

ALTER PROCEDURE [dbo].[GetEmployees]

AS

BEGIN

      SET NOCOUNT ON;

      SELECT E.ID, E.Name,E.DOB, E.DOJ,E.Phone,E.Email, D.Name as Department, E.Salery

       From Employee E INNER JOIN DEPARTMENT D

      On E.DepartmentID = D.ID

END

If you do not have a Stored procedure, create one. Once you have the Procedure in place, follow the below steps to map the Procedure to the ADO.Net Entity Framework.

1. Open the Model class (EmployeeModel.edmx)
2. Right click on the Designer and select Update Model from Database
3. This opens up the Object Browse, Expand the Stored Procedure node in the Object Tree and select the Stored Procedures to be used.

4. The Selected Procedures get added to the Stored procedure Node, in the Model Browser
5. Right click on the Designer and select Add -> Function Import
6. In the Popup which appears, select the Stored procedure Name, and give a Mapping Function Name.
7. Set the Return Type to one of the Objects in the Model, here I will set it as Employee.

8. The new Function gets added to the Model under the Function Imports node in the Model Browser.
9. We have now created the mapping, call the mapping function from the code as follows.

EmployeesEntities dbContext = new EmployeesEntities();

var emp = dbContext.GetEmployees();

//

grdEmployees.DataSource = emp.ToList();

grdEmployees.DataBind();

10. That’s it we have called a Stored Procedure using ADO.Net Entity Framework.

One limitation of this approach is that the Entity Framework expects the Stored Procedure to return results which are compatible with Entity Framework. In this case, the procedure was returning results which were compatible with the Employee type, hence it works fine, but if I want to create a custom statement by joining other tables and want to return the result then this approach will not work.

If the procedure returns a custom type which is not compatible with the Entities defined then this approach then we will have to define a Complex Type and map the return value of the procedure to the Complex Type.

The Complex Type definition is available only in Entity Framework 4 and above versions which ships with .Net Framework 4 and Visual Studio 2010. .Net Framework 3.5 SP1 with Visual Studio 2008 SP1 uses Entity Framework 3.5, which does not have the Complex Type definition option.