How to: Map Modification Functions to Stored Procedures (Entity Data Model Tools)

This topic describes how to use the ADO.NET Entity Data Model Designer (Entity Designer) to map the insert, update, and delete operations of an entity type to stored procedures.

The Entity Framework generates a class derived from ObjectContext that represents the entity container in the conceptual model. (The name of the derived class is the name of the EntityContainer in the conceptual model file.) This class exposes a SaveChanges method that 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.

Note
If you do not map all three of the insert, update, or delete operations of a entity type to stored procedures, the unmapped operations will fail if executed at runtime and an UpdateException is thrown.

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.

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 delete operation is called, an OptimisticConcurrencyException will be thrown.

See Also

Tasks

Walkthrough: Mapping an Entity to Stored Procedures (Entity Data Model Tools)