Database Reference
In-Depth Information
// 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
