Database Reference
In-Depth Information
How It Works
We updated the model with the stored procedures we created in the database. This makes the stored procedures
available for use in the model. Once we have the stored procedures available in the model, we mapped them to the
Insert, Update, and Delete actions for the entity.
In this recipe, the stored procedures are about as simple as you can get. They take in properties as parameters
and perform the action. For the Insert stored procedure, we need to return the stored generated key for the entity. In
this recipe, the stored generated key is just an identity column. We need to return this from the stored procedure for
the Insert action and map this returned value to the AthleteId property. This is an important step. Without this, Entity
Framework would not be able to get the entity key for the instance of the Athlete entity just inserted.
You may ask, “When do I map stored procedures to the actions?” In most cases, Entity Framework will generate
efficient code for the Insert, Update, and Delete actions. You may also be wondering, “When would I ever need to
replace this with my own stored procedures?” Here are the best-practice answers to this question.
•
Your company requires you to use stored procedures for some or all of the Insert, Update, or
Delete activity for certain tables.
•
You have additional tasks to do during one or more of the actions. For example, you might
want to manage an audit trail or perform some complex business logic, or perhaps you need
to leverage a user's privileges to execute stored procedures for security checking.
•
Your entity is based on a QueryView (see Chapter 6 and Chapter 15) that requires you to map
some or all of the actions to stored procedures.
The code in Listing 10-22 demonstrates inserting, deleting, and updating in the model. The code isn't any different
because of the mapping of the actions, and that's fine. The fact that we have replaced the code that Entity Framework
would have dynamically generated with our own stored procedures will not affect the code that uses the entity.
Listing 10-22.
Executing the Insert, Update, and Delete Actions
using (var context = new EF6RecipesContext())
{
context.Athletes.Add(new Athlete { Name = "Nancy Steward",
Height = 167, Weight = 53 });
context.Athletes.Add(new Athlete { Name = "Rob Achers",
Height = 170, Weight = 77 });
context.Athletes.Add(new Athlete { Name = "Chuck Sanders",
Height = 171, Weight = 82 });
context.Athletes.Add(new Athlete { Name = "Nancy Rodgers",
Height = 166, Weight = 59 });
context.SaveChanges();
}
using (var context = new EF6RecipesContext())
{
// do a delete and an update
var all = context.Athletes;
context.Delete(all.First(o => o.Name == "Nancy Steward"));
all.First(o => o.Name == "Rob Achers").Weight = 80;
context.SaveChanges();
}
