I produced a new set of short videos around ADO.NET Data Services and dropped them on to Channel 9. You can find them here;
and that list will grow as time goes in ( specifically, it’ll grow if I can get the publishing system to publish my next 4 videos for me as it’s not playing ball at the time of writing 🙂 ).
This is similar but not at all identical to this post about LINQ to SQL because it’s using a different framework and that has different capabilities.
Someone mailed and asked how we can detect concurrency problems with Entity Framework in order to ensure that when we submit changes to the DB we first;
I can use the same tables and scripts and so on that I used in the previous post about LINQ to SQL. So…if I’ve got this table in my DB;
create table Person ( id int identity primary key, firstName nvarchar(30), lastName nvarchar(30), timestamp )
with data;
insert person(firstname,lastname) values('first1', 'last1')
insert person(firstname,lastname) values('first2', 'last2')
insert person(firstname,lastname) values('first3', 'last3')
and I bring this into an Entity Framework based project using the usual route and I make sure that I’m checking the timestamp for concurrency by altering the properties on the diagram as below;
and then I can start to write some code (similar to my LINQ to SQL) code as below;
using (demoEntities context = new demoEntities())
{
Console.WriteLine("Enter a new surname");
string surname = Console.ReadLine();
var people = context.Person.ToList();
foreach (Person p in people)
{
p.lastName = surname;
}
Console.WriteLine("Now's the time to cause a concurrency violation");
Console.ReadLine();
try
{
context.SaveChanges();
}
catch (OptimisticConcurrencyException ex)
{
foreach (ObjectStateEntry item in ex.StateEntries)
{
Console.WriteLine("Found an item");
}
}
}
The “problem” that I’ve got here is that there’s no version of SaveChanges on the ObjectContext that allows for you to continue when you hit a concurrency error making it hard for me to build a list of “all the concurrency problems”.
So, if I cause a concurrency problem at the Console.ReadLine() above with two of my entities by doing something like;
update person set lastName='anything' where id=2 update person set lastName='else' where id=3
then what I’m going to see is 1 an exception containing the details of a single concurrency exception. I’m not going to get to “see” the second concurrency exception until I clear the first one.
How can I clear the first one? By refreshing the entity’s original values from the DB whilst keeping my changes.
That’s all fine in that I can write code like this but if I wanted to build up a complete list of all my concurrency problems and then present them to a user as a list before making any changes to the data then this code;
using (demoEntities context = new demoEntities())
{
Console.WriteLine("Enter a new surname");
string surname = Console.ReadLine();
var people = context.Person.ToList();
foreach (Person p in people)
{
p.lastName = surname;
}
Console.WriteLine("Now's the time to cause a concurrency violation");
Console.ReadLine();
bool allSaved = false;
List<object> failedEntities = new List<object>();
while (!allSaved)
{
try
{
context.SaveChanges();
allSaved = true;
}
catch (OptimisticConcurrencyException ex)
{
// This will only iterate once.
foreach (ObjectStateEntry item in ex.StateEntries)
{
context.Refresh(RefreshMode.ClientWins, item.Entity);
failedEntities.Add(item.Entity);
}
}
}
// Spot the problem? It's too late already...
Console.WriteLine("Encountered {0} concurrency problems along the way",
failedEntities.Count);
}
has an obvious problem in that by the time my code has figured out the list of entities that have concurrency problems it’s all too late because I’ve already submitted the changes to the DB without having had any opportunity to consult the user (i.e. the last call to SaveChanges will actually work).
The only way that I can think of stopping that happening is to ensure that the transaction will roll back in the case where SaveChanges fails ( this would happen anyway ) and the case where it succeeds ( this would not happen usually ).
Something along the lines of;
using (demoEntities context = new demoEntities())
{
Console.WriteLine("Enter a new surname");
string surname = Console.ReadLine();
var people = context.Person.ToList();
foreach (Person p in people)
{
p.lastName = surname;
}
Console.WriteLine("Now's the time to cause a concurrency violation");
Console.ReadLine();
bool allSaved = false;
while (!allSaved)
{
bool allTried = false;
List<object> failedEntities = new List<object>();
while (!allTried)
{
TransactionScope scope = new TransactionScope();
try
{
context.SaveChanges(false);
allTried = true;
if (failedEntities.Count == 0)
{
scope.Complete();
}
scope.Dispose();
}
catch (OptimisticConcurrencyException ex)
{
scope.Dispose();
// This will only iterate once.
foreach (ObjectStateEntry item in ex.StateEntries)
{
context.Refresh(RefreshMode.ClientWins, item.Entity);
failedEntities.Add(item.Entity);
}
}
}
if (!(allSaved = failedEntities.Count == 0))
{
Console.WriteLine("Hit {0} concurrency problems", failedEntities.Count);
foreach (object entity in failedEntities)
{
ObjectStateEntry stateEntry =
context.ObjectStateManager.GetObjectStateEntry(entity);
RefreshMode mode = PromptUserForEntityConflict(stateEntry);
context.Refresh(mode, entity);
}
}
}
// Finally, we can make current == original.
context.AcceptAllChanges();
}
with a couple of little functions;
private static RefreshMode PromptUserForEntityConflict(ObjectStateEntry entry)
{
RefreshMode mode = RefreshMode.StoreWins;
Console.WriteLine("Conflict on entity with key [{0}]",
EntityKeyToString(entry));
foreach (string propertyName in entry.GetModifiedProperties())
{
Console.WriteLine("\tProperty [{0}], your value [{1}], db value [{2}]",
propertyName, entry.CurrentValues[propertyName],
entry.OriginalValues[propertyName]);
}
Console.WriteLine("Do you want to keep your values [y] or db values [d]?");
mode = Console.ReadLine() == "y" ?
RefreshMode.ClientWins : RefreshMode.StoreWins;
return (mode);
}
private static string EntityKeyToString(ObjectStateEntry entry)
{
StringBuilder sb = new StringBuilder();
for (int i = 0; i < entry.EntityKey.EntityKeyValues.Length; i++)
{
sb.AppendFormat("{0}{1}", i > 0 ? "," : string.Empty,
entry.EntityKey.EntityKeyValues[i]);
}
return (sb.ToString());
}
This seems to be about the best I can do if I need to present the user with all (current) concurrency problems in a single go and also present them with their values versus the database values.
In a lot of systems, concurrency issues are rare (e.g. generally I’m hoping that when I’m updating my bank account details via a call centre there isn’t someone else also updating my details :-)) so this might not be such a common scenario but, with this implementation it means that if you have M modifications to update which cause N concurrency exceptions then you’ll need at least N+2 trips to the database (with quite a lot of rolled back transactions) to make this work.
Feel free to suggest improvements and I’ll add them here.
I came across a bit of a glitch in using timestamps for checking concurrency violations in LINQ to SQL and thought I’d share.
Say I’ve got a table like;
create table Person ( id int identity primary key, firstName nvarchar(30), lastName nvarchar(30), timestamp )
so, we have a simple table that has a timestamp on it and I want to use that to detect any concurrency problems that I might have in LINQ to SQL.
Let’s populate this table with some data;
insert person(firstname,lastname) values('first1', 'last1')
insert person(firstname,lastname) values('first2', 'last2')
insert person(firstname,lastname) values('first3', 'last3')
and then I can bring that into my LINQ to SQL environment and I can check what the concurrency options are set to;
So, you can see that we’ve got Update Check set to Always and that means that the timestamp will be added to any where clauses for Deletes or Updates and if a particular update doesn’t find any row to update because of that where clause then we have a concurrency violation.
Note also that Auto-Sync is set to Always which is a good thing because it means that if we insert an entity we’ll automatically get the timestamp back and if we update an entity we’ll similarly get an updated timestamp so that the timestamp in the original values of our entity in memory will represent the timestamp of the record when we queried/inserted/updated it and will only get “stale” if someone else were to update it (or delete it) in the meantime.
So, with LINQ to SQL we can write some code such as the stuff below which prompts for a new surname, changes the value in the entities that it has queried then tries to do a SubmitChanges and tries to make sure that it captures as many concurrency violations as it can ( the ContinueOnConflict parameter ).
using (DemoDataContext ctx = new DemoDataContext())
{
var people = ctx.Persons.ToList();
Console.WriteLine("Enter a new surname");
string surname = Console.ReadLine();
// Cause some changes.
foreach (Person p in people)
{
p.lastName = surname;
}
// HERE...
Console.WriteLine("Now's the time to cause a concurrency violation");
Console.ReadLine();
bool allSaved = false;
while (!allSaved)
{
try
{
ctx.SubmitChanges(ConflictMode.ContinueOnConflict);
allSaved = true;
}
catch (ChangeConflictException ex)
{
Console.WriteLine("Hit a conflict - fixing it!");
foreach (ObjectChangeConflict conflict in ctx.ChangeConflicts)
{
conflict.Resolve(RefreshMode.KeepChanges);
}
}
catch (Exception ex)
{
Console.WriteLine("Hit an unexpected exception [{0}]",
ex.Message);
}
}
}
When we hit the line of code marked HERE above, I go and run this piece of T-SQL;
update person set lastName='anything' where id=2 update person set lastName='else' where id=3
to cause concurrency problems for row number 2 and number 3 in my database.
What should happen;
However, that’s not what happens when the code does. What happens is that we hit an unexpected exception at 8 above and my Console.WriteLine writes it out;
Hit an unexpected exception [Value of member ‘timestamp’ of an object of type ‘Person’ changed.
What’s going on here? I think what’s happening here is that when we hit the call to SubmitChanges we go to the database to update 3 entities. The first update works so we update the timestamp. The 2nd update fails and so does the 3rd so we rollback the transaction.
However, we’ve updated the timestamp on that 1st entity.
We then go and resolve the 2 concurrency violations we’ve got before calling SubmitChanges again. It takes a look at that first entity which didn’t have a concurrency problem but does now have a modified timestamp and still needs updating in the database ( because the transaction was rolled back ) and says “Hey, you’re not meant to change generated columns like this!!”.
Got it?
The timestamp is only being updated because we have allowed the Auto-Sync property to be default to Always in the mapping information. That means “re-sync this column whenever we insert, update”.
We could change this so that we only do Auto-Sync on Insert by setting;
Now, our code as it originally stands is going to work just fine if I re-run it under the same conditions where I cause a concurrency violation at the line marked HERE because when we hit the initial call to SubmitChanges which throws a ChangeConflictException we catch that exception, call Resolve on the 2 rows with the problems and then we call SubmitChanges again and we’ve never updated the timestamp on row number 1.
However…this isn’t a free ride.
The problem with switching Auto-Sync to OnInsert means that whenever we actually do a successful update we won’t update the timestamp to reflect the latest value that we just generated in the database.
So, whenever we call SubmitChanges for subsequent updates we will see superfluous concurrency exceptions for every row that didn’t hit a concurrency exception the last time we called SubmitChanges.
That is, this code (which runs forever) will work;
using (DemoDataContext ctx = new DemoDataContext())
{
var people = ctx.Persons.ToList();
while (true)
{
Console.WriteLine("Enter a new surname");
string surname = Console.ReadLine();
// Cause some changes.
foreach (Person p in people)
{
p.lastName = surname;
}
// HERE...
Console.WriteLine("Now's the time to cause a concurrency violation");
Console.ReadLine();
bool allSaved = false;
while (!allSaved)
{
try
{
ctx.SubmitChanges(ConflictMode.ContinueOnConflict);
Console.WriteLine("Submitted changes");
allSaved = true;
}
catch (ChangeConflictException ex)
{
Console.WriteLine("Hit a conflict - fixing it!");
foreach (ObjectChangeConflict conflict in ctx.ChangeConflicts)
{
conflict.Resolve(RefreshMode.KeepChanges);
}
}
catch (Exception ex)
{
Console.WriteLine("Hit an unexpected exception [{0}]",
ex.Message);
}
}
}
}
and it’ll work regardless of whether I cause concurrency problems at the line marked HERE. However, once the loop has executed once we run the risk of hitting “false” concurrency problems at the call to SubmitChanges for any rows that have out of date timestamps because we did not update them the last time we did an update.
The other thing that comes to mind here is that this all depends on the lifetime of your DataContext. If you just use it for one “unit of work” then this isn’t really going to bite you in the same way because Auto-Sync doesn’t seem so relevant if you’re going to throw your DataContext away after you call SubmitChanges anyway.
And the last thing is that this won’t affect you at all unless you’re using generated columns like timestamps for concurrency checking.
Mike Taulty 