How to: Map Modification Functions to Stored Procedures
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.
When you map insert, update, and delete operations of an entity type to stored procedures, you must do the following:
Map all three operations to stored procedures.
Consistently map associations. If a stored procedure maps to an association for one operation, the other two operations must also map to the association.
Map related entities that have a one-to-one relationship to stored procedures. If the entity is related to other entities by a one-to-many or many-to-many relationship, you do not have to map the related entities to stored procedures.
Mapping the Insert Operation to a Stored Procedure
To map the insert operation to a stored procedure
Double click the <model name>.edmx file in Solution Explorer.
The file opens in the Entity Designer.
Right-click the entity type for which you want to map the insert operation.
Select Stored Procedures Mapping.
The Map Entity to Functions view of the Mapping Details window appears.
Click <Select Insert Function>.
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.
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.
Click on <Add Result Binding>.
The field becomes editable.
Type the name of the parameter that contains data that was returned by the stored procedure.
Click the property field that corresponds to the parameter name.
The field becomes a drop-down list of properties.
Select the property to which you want the returned data mapped.
Repeat steps 7 through 10 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
Double click the <model name>.edmx file in Solution Explorer.
The file opens in the Entity Designer.
Right-click the entity type for which you want to map the update operation.
Select Stored Procedures Mapping.
The Map Entity to Functions view of the Mapping Details window appears.
Click <Select Update Function>.
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.
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.
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.
Click <Add Result Binding>.
The field becomes editable.
Type the name of the parameter that contains data that was returned by the stored procedure.
Click the property field that corresponds to the parameter name.
From the drop-down list, select the property to which you want the returned data mapped.
Optionally, repeat steps 8 through 11 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
Double click the <model name>.edmx file in Solution Explorer.
The file opens in the Entity Designer.
Right-click the entity type for which you want to map the delete operation.
Select Stored Procedures Mapping.
The Map Entity to Functions view of the Mapping Details window appears.
Click <Select Delete Function>.
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.
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.
See Also
Tasks
Walkthrough: Mapping an Entity to Stored Procedures
Other Resources
Stored Procedure Support (Entity Framework)
Application Code for Mapped Stored Procedures (Entity Framework)