Mapping functions
So far all our entity properties are mapped directly to database fields, and the Entity Framework is generating the code to insert, update and delete values. But as you probably know, many database administrators don’t allow this kind of direct access. For very good reasons having to do with maintaining the integrity of the data for which they’re responsible, they require you to perform these operations through stored procedures. The Entity Framework treats stored procedures as functions. You add them using the Update Wizard and connect them to the conceptual model in the database file. Let’s give it a whirl:
The first step is easy. Run the wizard the same way you did before, by right-clicking on a blank area of the primary designer window and choosing Update Model from Database.
On the Add tab of the wizard, select the CreateRecipe, DeleteRecipe and UpdateRecipe stored procedures, as shown. (The other stored procedures that the wizard lists were added by Visual Studio and the SQL Server Management Studio. You can ignore them.)
Click Finish. Once again, the wizard will update the SSDL and MSL but leave your conceptual model alone, so you won’t see any changes.
Make sure the Recipe entity is still selected on the primary designer surface, and then click the second button on the left side of the Mapping Details Window to display the Map Entity to Functions Pane.
Click in the <Select Insert Function> cell, and a list of the stored procedures we’ve imported into the model will be displayed. Choose CreateRecipe.
After you choose the stored procedure (if you choose the wrong one, just choose a different one from the list), the Mapping Details Window will display a list of the parameters that were defined when the stored procedure was created.
We need to tell Entity Framework how to map the stored procedure parameters to the entity properties. When you click in the Property column, the Mapping Details Window will display a list of properties for you to choose from. Go ahead and fill it out now, using the screenshot as an example.
One last step. The RecipeID field is an identity field, which means the value is generated by the database. We need to store the generated value in the entity instance to make sure our in-memory data matches up with the rows of the table. The stored procedure returns that value as an output parameter called NewRecipeID, so all we have to do is tell the Entity Framework about it. Type NewRecipeID in the cell labeled <Add Result Binding> and then press the Tab key. The Mapping Details Window will add RecipeID for you, since it’s the entity key for the Recipe entity.
On Your Own
The UpdateRecipe stored procedure needs to be mapped to the Update function. It doesn’t return any values (although the corresponding procedure in a production database might return the number of rows affected).
Try adding it now.
Thinking Hat?
How’d you do? Here’s what the Mapping Details Window should look when you’re finished:
On Your Own
It isn’t necessary to map every operation to a stored procedure. Sometimes you can’t delete a row at all, for example. But our database does have stored procedures for the full set of operations, so now that you’re an expert at this, why don’t you go ahead and add the DeleteRecipe function to the Mapping Details Window. Like the UpdateRecipe stored procedure, it doesn’t have an output value, and it only has one input value (since only the key is required to identify the row to be deleted).
Take a Break
Once you’ve completed the On Your Own exercise, why don’t you take a break before you complete the Review and we move on to the Model Browser Window?