Concurrency - Entity Framework 6 Recipes - page 481

create procedure Chapter14.DeleteAgent
(@Name varchar(50), @TimeStamp_Original TimeStamp, @RowsAffected int OUTPUT)
as
begin
delete Chapter14.Agent where Name = @Name and TimeStamp = @TimeStamp_Original
set @RowsAffected = @@ROWCOUNT
end

2. Override the OnModelCreating method in your code-first DbContext class, and call

Entity<Agent>().MapToStoredProcedures() to map the stored procedures to the insert,
update, and delete actions on your agent model, as shown in Listing 14-5.

Listing 14-5. Overriding DbContext.OnModelCreating() to Map Stored Procedures to Insert, Update,
and Delete Operations

protected override void OnModelCreating(DbModelBuilder modelBuilder)
{
base.OnModelCreating(modelBuilder);

modelBuilder
.Entity<Agent>()
.MapToStoredProcedures(agent =>
{
agent.Insert(i => i.HasName("Chapter14.InsertAgent"));
agent.Update(u => u.HasName("Chapter14.UpdateAgent"));
agent.Delete(d => d.HasName("Chapter14.DeleteAgent"));
});

}

The code in Listing 14-6 demonstrates inserting and updating the database using the stored procedures. In the
code, we update the phone numbers for both agents. For the first agent, we update the agent in the object context and
save the changes. For the second agent, we do an out-of-band update before we update the phone using the object
context. When we save the changes, Entity Framework throws an OptimisticConcurrencyException , indicating that
the underlying database row was modified after the agent was materialized in the object context.

Listing 14-6. Demonstrating How Entity Framework and Our Insert and Update Stored Procedures Respond
to a Concurrency Violation

using (var context = new EF6RecipesContext())
{
context.Agents.Add(new Agent { Name = "Phillip Marlowe",
Phone = "202 555-1212" });
context.Agents.Add(new Agent { Name = "Janet Rooney",
Phone = "913 876-5309" });
context.SaveChanges();
}

using (var context = new EF6RecipesContext())
{
// change the phone numbers
var agent1 = context.Agents.Single(a => a.Name == "Janet Rooney");