How to: Map Modification Functions to Stored Procedures

[This page is specific to the latest version of the Entity Framework. The latest version is available as the 'Entity Framework' NuGet package. For more information, see Entity Framework Releases and Versioning.]

This topic describes how to use the Entity Designer to map the insert, update, and delete operations of an entity type to stored procedures.

When you call the SaveChanges method, the Entity Framework triggers updates to the underlying database. These updates can use SQL statements that are automatically generated by the system (the default), or they can use stored procedures that are specified by the developer. The application code you use to create, update, and delete entities is the same whether or not you use stored procedures to update the database.

The procedures below assume that you have an .edmx file open in the Entity Designer.

Mapping the Insert Operation to a Stored Procedure

To map the insert operation to a stored procedure

1. On the Entity Designer surface or in the Model Browser Window, right-click the entity type for which you want to map the insert operation and select Stored Procedures Mapping .

   The Map Entity to Functions view of the Mapping Details window appears.

2. Click <Select Insert Function> .

3. From the drop-down list, select the stored procedure to which the insert operation will be mapped.

   The window is populated with default mappings between entity properties and stored procedure parameters.

4. For each stored procedure parameter, modify the mapping as appropriate by clicking the corresponding property field and selecting the appropriate property from the drop-down list.

   Note

   All entity keys must have mappings. If the stored procedure uses an INSERT statement, the entity key will typically be mapped to the primary key that was created when the new row was inserted. The next few steps describe how to map data that is returned by the stored procedure to entity properties.

5. Click on <Add Result Binding> .

   The field becomes editable.

6. Type the name of the parameter that contains data that was returned by the stored procedure.

7. Click the property field that corresponds to the parameter name.

   The field becomes a drop-down list of properties.

8. Select the property to which you want the returned data mapped.

   Note

   For integer-valued output parameters, the Rows Affected Parameter checkbox is enabled. If the checkbox is selected for a parameter and the value returned is zero when the insert operation is called, an OptimisticConcurrencyException will be thrown.

9. Repeat steps 5 through 8 for each returned value and unmapped property.

The insert operation for the selected entity type is now mapped to a stored procedure.

Mapping the Update Operation to a Stored Procedure

To map the update operation to a stored procedure

1. On the Entity Designer surface or in the Model Browser Window, right-click the entity type for which you want to map the insert operation and select Stored Procedures Mapping .

   The Map Entity to Functions view of the Mapping Details window appears.

2. Click <Select Update Function> .

3. From the drop-down list, select the stored procedure to which the update operation will be mapped.

   The window is populated with default mappings between entity properties and stored procedure parameters.

4. For each stored procedure parameter, modify the mapping as appropriate by clicking the corresponding property field and selecting the appropriate property from the drop-down list.

5. For each property, you can optionally check the box in the Use Original Value column.

   The Use Original Value option enables you to use concurrency control. If the Use Original Value option is selected for a property, the value of the property that was read from the database will be passed to the specified stored procedure parameter. Note that both the original and current values of a property can be passed to different parameters.

   Note

   When you map the update operation, you can map data that was returned by the stored procedure to entity properties. The next few steps describe how to map data returned by the stored procedure to entity properties.

6. Click <Add Result Binding> .

   The field becomes editable.

7. Type the name of the parameter that contains data that was returned by the stored procedure.

8. Click the property field that corresponds to the parameter name.

9. From the drop-down list, select the property to which you want the returned data mapped.

   Note

   For integer-valued output parameters, the Rows Affected Parameter checkbox is enabled. If the checkbox is selected for a parameter and the value returned is zero when the update operation is called, an OptimisticConcurrencyException will be thrown.

10. Optionally, repeat steps 6 through 9 for each returned value.

The update operation for the selected entity type is now mapped to a stored procedure.

Mapping the Delete Operation to a Stored Procedure

To map the delete operation to a stored procedure

1. On the Entity Designer surface or in the Model Browser Window, right-click the entity type for which you want to map the insert operation and select Stored Procedures Mapping .

   The Map Entity to Functions view of the Mapping Details window appears.

2. Click <Select Delete Function> .

3. From the drop-down list, select the stored procedure to which the delete operation will be mapped.

   The window is populated with default mappings between entity properties and stored procedure parameters.

4. For each stored procedure parameter, modify the mapping as appropriate by clicking the corresponding property field and selecting the appropriate property from the drop-down list.

   Note

   You must map associations to the stored procedure parameters. Associations are available for selection from the property drop-down list.

Build Date:

2013-06-19