Problem

You want to use optimistic concurrency with an entity in your model.

Solution

Let’s suppose you have a model like the one shown in Figure 14-1.

The Product entity describes products in your application. You want to throw an exception if an intermediate update occurs between the time you retrieve a particular product entity and the time an update is performed in the database. To implement that behavior, do the following:

1. Add a column of type RowVersion to the table mapped to the Product entity.
2. Right-click the design surface, and select Update Model from Database. Update the model with the newly changed table. The updated model is shown in Figure 14-2.

   

   Figure 14-2. The updated model with the newly added TimeStamp property

3. Right-click the TimeStamp property and select Properties. Change its Concurrency Mode property to Fixed.

The code in Listing 14-1 demonstrates that changing the underlying row in the table between the time the product entity is materialized and the time we update the table from changes in the product entity throws an exception.

Listing 14-1.  Throwing an Exception If Optimistic Concurrency Is Violated

using (var context = new EF6RecipesContext())
{
    context.Products.Add(new Product
                             {
                                 Name = "High Country Backpacking Tent",
                                 UnitPrice = 199.95M
                             });
 
    context.SaveChanges();
}
 
using (var context = new EF6RecipesContext())
{
    // get the product
    var product = context.Products.SingleOrDefault();
    Console.WriteLine("{0} Unit Price: {1}", product.Name,
                       product.UnitPrice.ToString("C"));
 
    // update out of band
    context.Database.ExecuteSqlCommand(@"update chapter14.product set
            unitprice = 229.95 where productId = @p0", product.ProductId);
 
    // update the product via the model
    product.UnitPrice = 239.95M;
    Console.WriteLine("Changing {0}'s Unit Price to: {1}", product.Name,
                       product.UnitPrice.ToString("C"));
 
    try
    {
        context.SaveChanges();
    }
    catch (DbUpdateConcurrencyException ex)
    {
        Console.WriteLine("Concurrency Exception! {0}", ex.Message);
    }
    catch (Exception ex)
    {
        Console.WriteLine("Exception! {0}", ex.Message);
    }
}

The following is the output of the code in Listing 14-1:

High Country Backpacking Tent Unit Price: $199.95
Changing High Country Backpacking Tent's Unit Price to: $239.95
Concurrency Exception! Store update, insert, or delete statement affected an unexpected number of rows (0). Entities may have been modified or deleted since entities were loaded. Refresh ObjectStateManager entries.

How It Works

Optimistic concurrency is a low-contention concurrency strategy because rows are not locked when they are updated; rather it is up to the application to check for changes in row data before updating the row. The downside to optimistic concurrency is the potential for overwriting data if the application does not check for changes in the data before updating the database. Pessimistic concurrency, on the other hand, is a high-contention concurrency strategy because rows are locked during updates. The disadvantage of pessimistic concurrency is clearly the potential for degraded application performance caused by row locking, or even worse, deadlocks.

Optimistic concurrency is not enabled by default when tables are imported into a model. To enable optimistic concurrency, change the Concurrency Mode property of one of the entity’s properties to Fixed. You do not have to use a TimeStamp property as we did in this recipe. You do need to choose a property that you know will be changed in every update to the underlying table. Typically, you would use a column whose value is generated by the database on each update. The TimeStamp column is a good candidate. If you choose another column, be sure to set the StoreGeneratedPattern property to Computed for the corresponding entity property. This will tell Entity Framework that the value is generated by the database. Entity Framework recognizes the TimeStamp data type as a Computed property.

In Listing 14-1, we inserted a new product into the database. We queried the model for the one product we inserted. Once we had the product, we updated the row out-of-band using the ExecuteSqlCommand() method to send a SQL update statement to the database changing the row. This out-of-band update simulates two users updating the same row simultaneously. On the database side, this update caused the UnitPrice to be changed to $229.95 and the TimeStamp column to be updated automatically by the database. After the out-of-band update, we changed the UnitPrice on the product in the database context to $239.95. At this point, the database context believes (incorrectly) that it has the most recent values for the product, including an update to the UnitPrice now set at $239.95. When we call SaveChanges(), Entity Framework generates an update statement with a where clause that includes both the ProductId and the TimeStamp values we have for the product. The value for this TimeStamp is the one retrieved when we read the product from the database before the out-of-band update. Because the out-of-band update caused the TimeStamp to change, the value for the TimeStamp column in the database is different from the value of the TimeStamp property on the product entity in the database context. The update statement will fail because no row is found in the table matching both the ProductId and the TimeStamp values. Entity Framework will respond by rolling back the entire transaction and throwing a DbUpdateConcurrencyException.

In responding to the exception, the code in Listing 14-1 printed a message and continued. This is probably not how you would handle a concurrency violation in a real application. One way to handle this exception is to refresh the entity with the current value of the concurrency column from the database. With the correct value for the concurrency column, a subsequent SaveChanges() will likely succeed. Of course, it might not for the same reason that it failed the first time, and you need to be prepared for this as well.

The DbUpdateConcurrencyException object has an Entries collection property, which contains a DbEntityEntry instance for each entity that fails to update. The DbEntityEntry class defines a Reload() method that will cause the entry to be updated with the values from the database (database wins), and all changes made to the entry in the database context are lost.

It is possible, however, to overwrite the entry's OriginalValues property such that SaveChanges() can be called on the database context without a concurrency violation, as shown in Listing 14-2.

Listing 14-2.  Resolving a Concurrency Conflict in a Client Wins Scenario

bool saveChangesFailed;
do
{
    saveChangesFailed = false;
    try
    {
        context.SaveChanges();
    }
    catch (DbUpdateConcurrencyException ex)
    {
        saveChangesFailed = true;
        var entry = ex.Entries.Single();
        entry.OriginalValues.SetValues(entry.GetDatabaseValues());
    }
} while (saveChangesFailed);

In addition to the two aforementioned scenarios, it is possible to write custom code to update a conflicting entity with data from both the database and the client, or to allow user intervention to resolve data conflicts.

Problem

You want to use optimistic concurrency when using stored procedures for the insert, update, and delete actions.

Solution

Let’s suppose that we have a table like the one shown in Figure 14-3 and the entity shown in Listing 14-3, which is mapped to the table.

Listing 14-3.  The Agent Model

[Table("Agent", Schema = "Chapter14")]
public class Agent
{
    [Key]
    [MaxLength(50)]
    public string Name { get; set; }
 
    [Required]
    [MaxLength(50)]
    public string Phone { get; set; }
 
    [Timestamp]
    public byte[] TimeStamp { get; set; }
}

You want to use stored procedures to handle the insert, update, and delete actions for the model. These stored procedures need to be written so that they leverage the optimistic concurrency support provided in Entity Framework. Do the following to create the stored procedures and map them to actions:

1. Create the stored procedures in the database using the code in Listing 14-4.

   Listing 14-4.  Stored Procedures for the Insert, Update, and Delete actions

   create procedure Chapter14.InsertAgent
   (@Name varchar(50), @Phone varchar(50))
   as
   begin
     insert into Chapter14.Agent(Name, Phone)
     output inserted.TimeStamp
     values (@Name, @Phone)
   end
   go
   create procedure Chapter14.UpdateAgent
   (@Name varchar(50), @Phone varchar(50), @TimeStamp_Original TimeStamp, @RowsAffected int OUTPUT)
   as
   begin
     update Chapter14.Agent set Phone = @Phone where Name = @Name
     and TimeStamp = @TimeStamp_Original
     set @RowsAffected = @@ROWCOUNT
   end
   go
   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");
    var agent2 = context.Agents.Single(a => a.Name == "Phillip Marlowe");
    agent1.Phone = "817 353-4458";
    context.SaveChanges();
 
    // update the other agent's number out-of-band
    context.Database.ExecuteSqlCommand(@"update Chapter14.agent
         set Phone = '817 294-6059' where name = 'Phillip Marlowe'");
 
    // now change it using the model
    agent2.Phone = "817 906-2212";
    try
    {
        context.SaveChanges();
    }
    catch (DbUpdateConcurrencyException ex)
    {
        Console.WriteLine("Exception caught updating phone number: {0}",
                           ex.Message);
    }
}
 
using (var context = new EF6RecipesContext())
{
    Console.WriteLine("-- All Agents --");
    foreach (var agent in context.Agents)
    {
        Console.WriteLine("Agent: {0}, Phone: {1}", agent.Name, agent.Phone);
    }
}

The following is the output of the code in Listing 14-6. Notice that we caught the exception thrown during SaveChanges() and printed the exception message:

Exception caught updating phone number: Store update, insert, or delete statement
affected an unexpected number of rows (0). Entities may have been modified or deleted since entities were loaded. Refresh ObjectStateManager entries.
-- All Agents --
Agent: Janet Rooney, Phone: 817 353-4458
Agent: Phillip Marlowe, Phone: 817 294-6059

How It Works

The key to leveraging the concurrency infrastructure in Entity Framework is in the implementation of the stored procedures (see Listing 14-4) and in how we mapped the input parameters and the result values. Let’s look at each stored procedure.

The InsertAgent() procedure takes in the name and phone number for the agent and executes an insert statement. This results in the database computing a timestamp value that is inserted into the table along with the name and phone number. The select statement retrieves this newly generated timestamp. After the insert, the entity has the current values for the name and phone number and the newly generated timestamp. At that instant, the entity is in sync with the database.

With the UpdateAgent() procedure, Entity Framework automatically maps our Name and Phone properties to the stored procedure's parameters as long as the property names and parameter names match. However, we've named the timestamp parameter @TimeStamp_Original. This ensures that the original value for the TimeStamp property is sent to the database when we call the stored procedure. The where clause on the update statement includes the timestamp; if the timestamp value for the row in the database is different from the value in the entity, the update will fail. Because no rows are updated, Entity Framework responds by throwing a DbUpdateConcurrencyException. If the update succeeds, the new timestamp value is mapped to the TimeStamp property on the entity. At this point, the entity and row in the table are once again synchronized.

For the DeleteAgent() procedure, Entity Framework once again maps the Name  and TimeStamp properties to the parameters of the procedure automatically. The where clause on the delete statement includes the primary key, Name, and the timestamp value; this ensures that the row is deleted if, and only if, no intermediate update of the row has occurred. If no row is deleted, Entity Framework will respond with a DbUpdateConcurrencyException.

Entity Framework relies on each of these stored procedures returning some indication of the number of rows affected by the operation. We’ve crafted each procedure to return this value either using a select statement that returns either one or zero rows, or the row count from the statement.

There are three ways, in order of precedence, that Entity Framework interprets the number of rows affected by a stored procedure: the return value from ExecuteNonQuery(), the number of rows returned, or an explicit output parameter (see Recipe 14-5).

The code in Listing 14-6 demonstrates that an intervening update, which we do out-of-band with the ExecuteSqlCommand() method, causes a concurrency violation when we update Phillip Marlowe’s phone number.

Problem

Your application requires fast concurrent access with as little database overhead as possible, so you want to read uncommitted data using LINQ to entities.

Solution

Suppose you have an Employee entity like the one shown in Figure 14-4. You want to insert a new employee, but before the row is committed to the database, you want to read the uncommitted row into a different object context. To do this, create nested instances of the TransactionScope class and set the IsolationLevel of the innermost scope to ReadUncommitted, as shown in Listing 14-7. You will need to add a reference in your project to System.Transactions.

Listing 14-7.  Creating Nested TransactionScopes and Setting the IsolationLevel to ReadUncommitted

using (var context = new EF6RecipesContext())
{
    using (var scope1 = new TransactionScope())
    {
        // save, but don't commit
        var outerEmp = new Employee { Name = "Karen Stanfield" };
        Console.WriteLine("Outer employee: {0}", outerEmp.Name);
        context.Employees.Add(outerEmp);
        context.SaveChanges();
 
        // second transaction for read uncommitted
        using (var innerContext = new EF6RecipesContext())
        {
            using (var scope2 = new TransactionScope(
                TransactionScopeOption.RequiresNew,
                new TransactionOptions {
                   IsolationLevel = IsolationLevel.ReadUncommitted }))
            {
                var innerEmp = innerContext.Employees
                                .First(e => e.Name == "Karen Stanfield");
                Console.WriteLine("Inner employee: {0}", innerEmp.Name);
                scope1.Complete();
                scope2.Complete();
            }
        }
    }
}

The following is the output of the code in Listing 14-7:

Outer employee: Karen Stanfield
Inner employee: Karen Stanfield

How It Works

In SQL, one of the common ways of reading uncommitted data is to use the NOLOCK query hint. However, Entity Framework does not support the use of hints. In Listing 14-7, we used a TransactionScope with the IsolationLevel set to ReadUncommitted. This allowed us to read the uncommitted data from the outer TransactionScope. We did this in a fresh data context.

Problem

You want to make sure that changes to an object succeed regardless of any intermediate changes to the database.

Solution

Suppose you have a model like the one shown in Figure 14-5.

Our model represents posts by users of an Internet forum. Moderators of forums often want to review posts and possibly change or delete them. The changes a moderator makes need to take precedence over any changes made by the forum’s users. In general, this can be implemented without much concern for concurrency, except when the user makes a change between the time the moderator retrieves the post and the when the moderator calls SaveChanges() to commit a change, such as a delete, to the database. In this case, we want the moderator’s changes to overwrite the user’s changes. We want the moderator to win.

To implement this, follow the pattern shown in Listing 14-8. Be sure to set the Concurrency Mode on the TimeStamp property to Fixed.

Listing 14-8.  Implementing Last Record Wins

int postId = 0;
using (var context = new EF6RecipesContext())
{
    // post is created
    var post = new ForumPost { ForumUser = "FastEddie27", IsActive = false,
                  Post = "The moderator is a great guy." };
    context.ForumPosts.Add(post);
    context.SaveChanges();
    postId = post.PostingId;
}
 
using (var context = new EF6RecipesContext())
{
    // moderator gets post to review
    var post = context.ForumPosts.First(p => p.PostingId  == postId);
    Console.WriteLine("Post by {0}: {1}", post.ForumUser, post.Post);
 
    // poster changes post out-of-band
    Context.Database.ExecuteSqlCommand(@"update chapter14.forumpost
             set post='The moderator''s mom dresses him funny.'
             where postingId = @p0", new object[] { postId.ToString() });
    Console.WriteLine("Fast Eddie changes the post");
 
    // moderator doesn't trust Fast Eddie
    if (string.Compare(post.ForumUser, "FastEddie27") == 0)
        post.IsActive = false;
    else
        post.IsActive = true;
 
    try
    {
        // refresh any changes to the TimeStamp
        var postEntry = context.Entry(post);
        postEntry.OriginalValues.SetValues(postEntry.GetDatabaseValues());
        context.SaveChanges();
        Console.WriteLine("No concurrency exception.");
    }
    catch (DbUpdateConcurrencyException exFirst)
    {
        try
        {
            // try one more time.
            var postEntry = context.Entry(post);
            postEntry.OriginalValues.SetValues(postEntry.GetDatabaseValues());
            context.SaveChanges();
        }
        catch (DbUpdateConcurrencyException exSecond)
        {
            // we tried twice...do something else
        }
    }
}

The following is the output of the code in Listing 14-8:

Post by FastEddie27: The moderator is a great guy.
Fast Eddie changes the post
No concurrency exception.

How It Works

The TimeStamp property is marked for concurrency because its ConcurrencyMode is set to Fixed. As part of the update statement, the value of the TimeStamp property is checked against the value in the database. If they differ, Entity Framework will throw a DbUpdateConcurrencyException. We’ve seen this behavior in the previous recipes in this chapter. What’s different here is that we want the change from the client, in this case the moderator, to overwrite the newer row in the database. We do this by repeating a particular strategy; however, even using this strategy, it's possible that we will not be able to update our data successfully.

The strategy we use in Listing 14-8 is to obtain the DbEntityEntry that is tracking changes to our ForumPost object and then refresh the OriginalValues property with the values currently in the data store. Armed with the latest TimeStamp property, our call to SaveChanges() should succeed. There is a chance, especially in a highly concurrent environment, that some intervening update could occur to change the row before our update hits the database. If this occurs, Entity Framework will throw a DbUpdateConcurrencyException. If that should occur, we try to repeat our DbEntityEntry refresh in the catch block and call SaveChanges() again.

Even with these two approaches in place, it is still possible for an intervening update to occur between the refresh and the time the update is executed on the database.

Problem

You want to return the number of rows affected by a stored procedure through an output parameter.

Solution

Entity Framework uses the number of rows affected by an operation to determine whether the operation succeeded or the operation failed because of a concurrency violation. When using stored procedures (see Recipe 14-2), one of the ways to communicate the number of rows affected by an operation is to return this value as an output parameter of the stored procedure.

Let’s suppose you have a model like the one shown in Figure 14-6.

To return the number of rows affected by the stored procedures mapped to the insert, update, and delete actions, do the following:

1. Create the stored procedures in the database using the code in Listing 14-9.

   Listing 14-9.  The Stored Procedures for the Insert, Update, and Delete Actions

   create procedure [Chapter14].[UpdateAccount]
   (@AccountNumber varchar(50), @Name varchar(50), @Balance decimal, @TimeStamp TimeStamp,  @RowsAffected int output)
   as
   begin
     update Chapter14.Account
     output inserted.TimeStamp
     set Name = @Name, Balance = @Balance
     where AccountNumber = @AccountNumber and TimeStamp = @TimeStamp
     set @RowsAffected = @@ROWCOUNT
   end
    
   go
    
   create procedure [Chapter14].[InsertAccount]
   (@AccountNumber varchar(50), @Name varchar(50), @Balance decimal,
    @RowsAffected int output)
   as
   begin
     insert into Chapter14.Account (AccountNumber, Name, Balance)
     output inserted.TimeStamp
     values (@AccountNumber, @Name, @Balance)
     set @RowsAffected = @@ROWCOUNT
   end
    
   go
    
   create procedure [Chapter14].[DeleteAccount]
   (@AccountNumber varchar(50), @TimeStamp TimeStamp, @RowsAffected int output)
   as
   begin
     delete Chapter14.Account where AccountNumber = @AccountNumber and
            TimeStamp = @TimeStamp
     set @RowsAffected = @@ROWCOUNT
   end

2. Right-click the design surface, and select Update Model from Database. Select the stored procedures you created in Step 1. Click Finish. This will import the stored procedures into the model.
3. View the Mapping Details window for the Account entity. Click the Map Entity to Functions button on the left side of the tool window. Map the insert, update, and delete actions to the stored procedures, as shown in Figure 14-7. Make sure that you map the Result column to the TimeStamp property for both the insert and update actions. For the update action, check the Use Original Value box for the procedure’s TimeStamp parameter. For each procedure, check the Rows Affected Parameter boxes, as shown in Figure 14-7.

When we call the SaveChanges() method in Listing 14-10 to update, insert, or delete, these actions are performed by the stored procedures in Listing 14-9 because of the mappings shown in Figure 14-7. Both the insert and update procedures return the updated TimeStamp value. This value is used by Entity Framework to enforce optimistic concurrency.

Listing 14-10.  Demonstrating the stored procedures mapped to the insert, update, and delete actions

using (var context = new EF6RecipesContext())
{
    context.Accounts.Add(new Account { AccountNumber = "8675309",
                                Balance = 100M, Name = "Robin Rosen"});
    context.Accounts.Add(new Account { AccountNumber = "8535937",
                                Balance = 25M, Name = "Steven Bishop"});
    context.SaveChanges();
}
 
using (var context = new EF6RecipesContext())
{
    // get the account
    var account = context.Accounts.First(a => a.AccountNumber == "8675309");
    Console.WriteLine("Account for {0}", account.Name);
    Console.WriteLine("	Previous Balance: {0}", account.Balance.ToString("C"));
 
    // some other process updates the balance
    Console.WriteLine("[Rogue process updates balance!]");
    context.Database.ExecuteSqlCommand(@"update chapter14.account set balance = 1000
                                   where accountnumber = '8675309'");
 
    // update the account balance
    account.Balance = 10M;
 
    try
    {
        Console.WriteLine("	New Balance: {0}", account.Balance.ToString("C"));
        context.SaveChanges();
    }
    catch (DbUpdateConcurrencyException ex)
    {
        Console.WriteLine("Exception: {0}", ex.Message);
    }
}
     

The following is the output of the code in Listing 14-10:

Account for Robin Rosen
        Previous Balance: $100.00
[Rogue process updates balance!]
        New Balance: $10.00
Exception: Store update, insert, or delete statement affected an unexpected number of rows (0). Entities may have been modified or deleted since entities were loaded. Refresh ObjectStateManager entries.

How It Works

The code in Listing 14-10 demonstrates using the stored procedures we’ve mapped to the insert, update, and delete actions. In the code, we purposely introduce an intervening update between the retrieval of an account object and saving the account object to the database. This rogue update causes the TimeStamp value to be changed in the database after we’ve materialized the object in the DbContext. This concurrency violation is detected by Entity Framework because the number of rows affected by the UpdateAccount() procedure is zero.

The mappings shown in Figure 14-7 tell Entity Framework how to keep the TimeStamp property correctly synchronized with the database and how to be informed of the number of rows affected by the insert, update, or delete actions. The Result Column for the insert and the update actions is mapped to the TimeStamp property on the entity. For the update action, we need to make sure that Entity Framework uses the original value from the entity when it constructs the statement invoking the UpdateAccount() procedure. These two settings keep the TimeStamp property synchronized with the database. Because our stored procedures return the number of rows affected by their respective updates in an output parameter, we need to check the Rows Affected Parameter box for this parameter for each of the action mappings.

Problem

You want to use optimistic concurrency in a model that uses Table per Type inheritance.

Solution

Let’s suppose you have the tables shown in Figure 14-8, and you want to model these tables using Table per Type inheritance and use optimistic concurrency to ensure that updates are persisted correctly. To create the model supporting optimistic concurrency, do the following:

1. Add a TimeStamp column to the Person table.
2. Create a new class that inherits from DbContext in your project.
3. Create new POCO entities for Person, Instructor, and Student, as shown in Listing 14-11. The Person entity should be abstract because we do not want to create a person entity directly, while both the Instructor and Student entities will inherit from the Person entity.
4. Add an auto-property of type DbSet<Person> to the DbContext subclass.

Listing 14-11.  Entity Classes Reflecting Our Table per Type Inheritance Model with the TimeStamp Property Added to the Person Class

[Table("Person", Schema = "Chapter14")]
public abstract class Person
{
    [Key]
    public int PersonId { get; set; }
 
    public string Name { get; set; }
 
    [Timestamp]
    public byte[] TimeStamp { get; set; }
}
 
[Table("Student", Schema = "Chapter14")]
public class Student : Person
{
    public DateTime? EnrollmentDate { get; set; }
}
 
[Table("Instructor", Schema = "Chapter14")]
public class Instructor : Person
{
    public DateTime? HireDate { get; set; }
}

The code in Listing 14-12 demonstrates what happens in the model when an out-of-band update happens.

Listing 14-12.  Testing the Model by Applying a Rogue Update

using (var context = new EF6RecipesContext())
{
    var student = new Student { Name = "Joan Williams",
                                EnrollmentDate = DateTime.Parse("1/12/2010") };
    var instructor = new Instructor { Name = "Rodger Keller",
                                HireDate = DateTime.Parse("7/14/1992") };
    context.People.Add(student);
    context.People.Add(instructor);
    context.SaveChanges();
}
 
using (var context = new EF6RecipesContext())
{
    // find the student and update the enrollment date
    var student = context.People.OfType<Student>()
                    .First(s => s.Name == "Joan Williams");
    Console.WriteLine("Updating {0}'s enrollment date", student.Name);
 
    // out-of-band update occurs
    Console.WriteLine("[Apply rogue update]");
    context.Database.ExecuteSqlCommand(@"update chapter14.person set name = 'Joan Smith'
          where personId =
          (select personId from chapter14.person where name = 'Joan Williams')");
 
    // change the enrollment date
    student.EnrollmentDate = DateTime.Parse("5/2/2010");
    try
    {
        context.SaveChanges();
    }
    catch (DbUpdateConcurrencyException ex)
    {
        Console.WriteLine("Exception: {0}", ex.Message);
    }
}

The following is the output of the code in Listing 14-12:

Updating Joan Williams's enrollment date
[Apply rogue update]
Exception: Store update, insert, or delete statement affected an unexpected number of rows (0). Entities may have been modified or deleted since entities were loaded. Refresh ObjectStateManager entries.

How It Works

In Listing 14-12, the code retrieves a student entity. An intervening update occurs to the Person table before the code updates the EnrollmentDate property on the entity and calls SaveChanges(). Entity Framework detects the concurrency violation when updating the tables in the database because the value in the TimeStamp column in the Person table does not match the TimeStamp value in the student entity. Entity Framework applies concurrency at the entity level. Before the Student table is updated, the Person table is updated with a meaningless or dummy update and the new TimeStamp value is obtained. This can be seen in the trace in Listing 14-13. If this update fails to affect any rows, Entity Framework knows that the underlying table was changed since the last read. This would cause Entity Framework to throw an OptimisticConcurrencyException.

Listing 14-13.  Entity Framework Updates the TimeStamp in the Base Table Prior to Performing the Update in the Derived Table

exec sp_executesql N'declare @p int
update [Chapter14].[Person]
output [Inserted].[TimeStamp]
set @p = 0
where (([PersonId] = @0) and ([TimeStamp] = @1))
select [TimeStamp]
from [Chapter14].[Person]
where @@ROWCOUNT > 0 and
[PersonId] = @0',N'@0 int,@1 binary(8)',@0=10,@1=0x0000000000007D19

Note that, if the rogue update occurred on the Student table in the database, the TimeStamp column in the Person table would not have been changed and Entity Framework would not have detected a concurrency violation. This is an important point to remember. The concurrency detection illustrated here extends just to rogue updates to the base entity.

Problem

You want to use Model First, and you want an entity to have a TimeStamp property for use in optimistic concurrency.

Solution

To use Model First and create an entity with a TimeStamp property, do the following:

1. Find the T4 Template that is used to generate the DDL for Model First. This file is located in Program FilesMicrosoft Visual Studio 10.0Common7IDEExtensionsMicrosoftEntity Framework ToolsDBGenSSDLToSQL10.tt. Copy this file, and rename this copy to SSDLToSQL10Recipe7.tt. Place the copy in the same folder as the original.
2. Replace the line that starts with [<#=Id(prop.Name)#>] with the code in Listing 14-14. We’ll use this modified T4 Template to generate the DDL for our database.

   Listing 14-14.  Replace the Line in the T4 Template with This Line

   [<#=Id(prop.Name)#>]
   <#if (string.Compare(prop.Name,"TimeStamp",true) == 0)
   {#>TIMESTAMP<#} else { #><#=prop.ToStoreType()#><#} #>
   <#=WriteIdentity(prop, targetVersion)#> <#=WriteNullable(prop.Nullable)#>
   <#=(p < entitySet.ElementType.Properties.Count - 1) ? "," : ""#>

3. Add a new ADO.NET Entity Data Model to your project. Start with an Empty Model.
4. Right-click the design surface, and select Add Entity. Name this new entity PhonePlan. Change the Key Property name to PhonePlanId. Click OK.
5. Add Scalar properties for Minutes, Cost, and TimeStamp. Change the type for the Minutes property to Int32. Change the type for the Cost property to Decimal.
6. Change the type of the TimeStamp property to Binary. Change its StoreGeneratedPattern to Computed. Change the Concurrency Mode to Fixed.
7. Right-click the design surface, and view the Properties. Change the DDL Generation Template to SSDLToSQL10Recipe7.tt. This is the template that you modified in step 2. Change the Database Schema Name to Chapter14.
8. Right-click the design surface, and click Generate Database from Model. Select the connection and click Next. The generated DDL is shown in the dialog box. Listing 14-15 shows an extract from the generated DDL that creates the PhonePlan table. Click Finish to complete the generation.

Listing 14-15.  The DDL that creates the PhonePlan table

-- Creating table 'PhonePlans'
CREATE TABLE [Chapter14].[PhonePlans] (
[PhonePlanId] int  IDENTITY(1,1) NOT NULL,
[Minutes] int   NOT NULL,
[Cost] decimal(18,0)   NOT NULL,
[TimeStamp] TIMESTAMP   NOT NULL
);
GO

How It Works

The TimeStamp data type is not a portable type. Not all database vendors support it. It is unlikely that this type will be supported at the conceptual layer in future versions of Entity Framework. However, future versions will likely improve the user experience in selecting or modifying the appropriate T4 template that will generate the DDL.