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.
Someone asked me today how you’d go about ensuring that timestamp columns in your database tables show up in your Entity Framework EDMX file with a Concurrency=Fixed attribute on them.
That is – it’s very likely that the timestamps are there on the table to enforce concurrency so why not default their Concurrency value to “Fixed” ?
It’s a good question but it’s not something that the tooling does as far as I’m aware so I tried to have together some LINQ to XML code that would make an attempt at it. I don’t claim that this is correct at all but it might be a starting point for this and similar, related pre-processing that you want to do on an EDMX file.
static void Main(string[] args)
{
XElement edmxFile = XElement.Load(args[0]);
XNamespace edmxNs = XNamespace.Get("http://schemas.microsoft.com/ado/2007/06/edmx");
XNamespace ssdlNs = XNamespace.Get("http://schemas.microsoft.com/ado/2006/04/edm/ssdl");
XNamespace mapNs = XNamespace.Get("urn:schemas-microsoft-com:windows:storage:mapping:CS");
XNamespace csdlNs = XNamespace.Get("http://schemas.microsoft.com/ado/2006/04/edm");
var timestampCols =
from ssdlProp in edmxFile.DescendantsAndSelf(ssdlNs + "Property")
join mapProp in edmxFile.DescendantsAndSelf(mapNs + "ScalarProperty")
on (string)ssdlProp.Attribute("Name") equals (string)mapProp.Attribute("ColumnName")
join csdlProp in edmxFile.DescendantsAndSelf(csdlNs + "Property")
on (string)mapProp.Attribute("Name") equals (string)csdlProp.Attribute("Name")
where (string)ssdlProp.Attribute("Type") == "timestamp" &&
(string)mapProp.Parent.Attribute("StoreEntitySet") == (string)ssdlProp.Parent.Attribute("Name") &&
(string)mapProp.Ancestors(mapNs + "EntitySetMapping").First().Attribute("Name")
== (string)csdlProp.Parent.Attribute("Name")
select csdlProp;
foreach (var item in timestampCols)
{
item.SetAttributeValue("ConcurrencyMode", "Fixed");
}
edmxFile.Save(args[0]);
}
I then used this inside of VS as a pre-build action to change my EDMX file prior to building the whole project. Seemed to work for my trivial example.
Feel free to take, borrow, tweak, ignore 🙂 Just don’t blame me ( of course ) if it breaks your EDMX file.
Mike Taulty 