Working with Stored Procedures in Entity Framework - Part 1

By default entity framework generates SELECT, INSERT, UPDATE, DELETE queries to perform the respective operations on the database. At times, however, you may want to deviate from this default behavior and use stored procedures for these operations. Additionally, you may want to call arbitrary stored procedures to perform some task at hand. To that end this two part article explains how stored procedures can be dealt with in Entity Framework.

Why use stored procedures?

There can be many reasons to use stored procedures over EF generated raw queries. Some of them include:

• Performance. Although performance depends on many factors this is often a reason to use stored procedures.
• It might be a company wide standard to use stored procedures over raw queries.
• Stored procedures doing some extra task in addition to plain INSERT, UPDATE and DELETE.
• You might be having a complex piece of functionality neatly wrapped in a stored procedure.

Whatever may be the reason it's clear that sooner or later you need to call stored procedures in entity framework.

Integrating stored procedures with Entity Framework

Under entity framework, stored procedures are referred as Function Imports. So, what you basically do is create stored procedures as usual and then add / import them to the entity data model. The stored procedures you wish to use can be categorized into two types:

• Stored procedures that you wish to use for INSERT, UPDATE and DELETE operations for a table.
• Stored procedures that you wish to call independently in order to execute some processing.

To begin with let's investigate the first type of stored procedures.

Using stored procedures for INSERT, UPDATE and DELETE operations

In this section you will configure entity data model for the Customers table of the Northwind database to use INSERT, UPDATE and DELETE queries. Note that you can't use a stored procedure for the SELECT operation. The following figure shows the model in the designer.

In this example our interest is the Customers table. Although you can create and configure INSERT, UPDATE and DELETE operations to use stored procedure, for the sake of simplicity let's create a stored procedure that updates a customer record. This stored procedure is given below:

CREATE PROCEDURE [dbo].[UpdateCustomer] 
	@CustomerID nchar(5),
	@CompanyName nvarchar(40),
	@ContactName nvarchar(30),
	@ContactTitle nvarchar(30) = NULL,
	@Address nvarchar(60) = NULL,
	@City nvarchar(15) = NULL,
	@Region nvarchar(15) = NULL,
	@PostalCode nvarchar(10) = NULL,
	@Country nvarchar(15),
	@Phone nvarchar(24) = NULL,
	@Fax nvarchar(24) = NULL
AS
BEGIN
	SET NOCOUNT ON;
	UPDATE Customers
	SET CompanyName=@CompanyName,
	ContactName=@ContactName,
	ContactTitle=@ContactTitle,
	Address=@Address,
	City=@City,
	Region=@Region,
	PostalCode=@PostalCode,
	Country=@Country,
	Phone=@Phone,
	Fax=@Fax
	WHERE CustomerID=@CustomerID;
END

As you can see the UpdateCustomer stored procedure simply updates an existing customer. This stored procedure is not yet the part of entity data model. To add it to the model, right click on the model and select "Update Model from Database" menu option. This will open the dialog as shown below:

As you can see the UpdateCustomer stored procedure is visible there and can be added to the model. Once added the stored procedure makes its appearance under Function Imports and Stored Procedures / Functions folders of the EF model browser as shown below:

To use this stored procedure for the UPDATE operation, select the Customer entity in the EF designer and right click on it to reveal its shortcut menu.

Then click on the Stored Procedure Mapping menu option to open Mapping Details window as shown below:

Select the Update function to be UpdateCustomers. This will cause the designer to map stored procedure parameters with the entity properties wherever they are the same, otherwise you need to map them manually. The following figure shows the final mapping of UpdateCustomer stored procedure with the Customer entity.

Notice that the CustomerID property is marked to use its Original Value since it is a primary key in the Customers table. This way the @CustomerID parameter of the stored procedure will receive the original value of the CustomerID property (i.e. the value at the time of populating the data).

This completes the stored procedure mapping. You can test the UPDATE operation by writing the following piece of code:

NorthwindEntities db = new NorthwindEntities();
Customer obj = db.Customers.Find("ALFKI");
obj.CompanyName = "Company 1";
obj.Country = "USA";
//set other properties here
db.SaveChanges();

If your stored procedure and mapping is correct you will find the record being updated in the database.

In this example you configured the UPDATE stored procedure. On the same lines you can configure INSERT and DELETE stored proecdures.

Dealing with identity values

Many times you may have to deal with tables whose primary key is an identity column. This column value is auto generated by the database during the INSERT operation and must be made available to the calling code after the successful insert operation.. Luckily, entity framework also allows you to map such values. Let's see how.

Consider the following stored procedure that inserts a record in the Employees table of the Northwind database.

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE InsertEmployee 
	@LastName nvarchar(20),
	@FirstName nvarchar(10),
	@Title nvarchar(30) = NULL,
	@TitleOfCourtesy nvarchar(25) = NULL,
	@BirthDate datetime = NULL,
	@HireDate datetime = NULL,
	@Address nvarchar(60) = NULL,
	@City nvarchar(15) = NULL,
	@Region nvarchar(15) = NULL,
	@PostalCode nvarchar(10) = NULL,
	@Country nvarchar(15) = NULL,
	@HomePhone nvarchar(24) = NULL,
	@Extension nvarchar(4) = NULL,
	@Photo image = NULL,
	@Notes ntext = NULL,
	@ReportsTo int = NULL,
	@PhotoPath nvarchar(255) = NULL
AS
BEGIN
	SET NOCOUNT ON;
	INSERT INTO [dbo].[Employees]
           ([LastName],[FirstName],[Title],[TitleOfCourtesy],
            [BirthDate],[HireDate],
	    [Address],[City],[Region],[PostalCode],[Country],
	    [HomePhone],[Extension],
            [Photo],[Notes],[ReportsTo],[PhotoPath])
	VALUES
           (@LastName, @FirstName,@Title,@TitleOfCourtesy,
	    @BirthDate,@HireDate,
	    @Address,@City,@Region,@PostalCode,@Country,
	    @HomePhone,@Extension,
	    @Photo,@Notes,@ReportsTo,@PhotoPath);

	SELECT SCOPE_IDENTITY() AS EmployeeID;
END
GO

The Employee table has a primary key (EmployeeID) that is an identity column. The above stored procedure, therefore, doesn't include it in the list of parameters. The EmployeeID of a newly inserted employee, however, must be returned to the calling code. This is accomplished by returning the SCOPE_IDENTITY() value as EmployeeID. Once the record is inserted you want to populate the EmployeeID property of the entity with this value. The following figure shows how InsertEmployee stored procedure is mapped with the Insert operation of the Employee entity.

While this figure looks quite similar as in the earlier example, there is a difference. Notice that under "Result Column Bindings" there is an entry for EmployeeID and it is mapped to the EmployeeID property of the entity. This way after inserting an employee record the EmployeeID property will be set to the SCOPE_IDENTITY().

Now, confirm this by adding an employee as follows:

NorthwindEntities db = new NorthwindEntities();
Employee emp = new Employee();
emp.LastName = "abcd";
emp.FirstName = "pqrs";
//set other properties here
db.Employees.Add(emp);
db.SaveChanges();
int empId = emp.EmployeeID;

You will find that empId variable holds the EmployeeID of the newly added employee in the database.

That's it for this article! In the Part 2 of this article I will explain how to call stored procedures that are not mapped with the INSERT, UPDATE or DELETE operations.