3

I am using ASP.NET to build a application for a retail company. I am using the Entity Framework (model-first) as my data access layer. I am using stored procedures to do my CRUD operations and all columns are mapped and seems to be correct as all CRUD functionality are working as expected.

But I am having concurrency issues with the DELETE operation.

I've added a TimeStamp column to the table I am doing the CRUD operation on. The UPDATE operation works fine as it is updating by primary key and the TimeStamp value. Thus if no rows are affected with the UPDATE operation, because of a change in the TimeStamp value, the Entity Framework throws a OptimisticConcurrencyException.

The DELETE operation works on the same principle as it is deleting by primary key and the TimeStamp value. But no exception is thrown when the TimeStamp value does not match between the entity and the database.

In the C# delete method I do retrieve the latest record first and then update the TimeStamp property to another TimeStamp value (It might be different to the retrieved value). After some investigation by using SQL Profiler I can see that the DELETE stored procedure is executed but the TimeStamp parameter that is passed to the stored procedure is the latest TimeStamp value and not the value that I have set the TimeStamp property to. Thus the record is deleted and the Entity Framework does not throw an exception.

Why would the Entity Framework still pass the retrieved TimeStamp value to the Stored Procedure and not the value that I have assigned the property? Is this be design or am I missing something?

Any help will be appreciated! (where is Julie Lerman when you need her! :-))

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Reynier Booysen
  • 281
  • 2
  • 14
  • Why do you update the timestamp manually? – Amiram Korach Aug 23 '12 at 14:01
  • It is a ASP.NET application so when the record is read the first time we save the TimeStamp value in the ViewState or Session. When the user eventually decides to update the record we update the TimeStamp property with this value so that we can do concurrency checks between the TimeStamp from the ViewState and the TimeStamp in the database. – Reynier Booysen Aug 24 '12 at 05:02
  • Found a similar issue [here](http://stackoverflow.com/questions/4699252/solving-optimistic-concurrency-update-problem-with-entity-framework). According to that you are not able to set the TimeStamp property because it is computed. But how would you implement concurrency checks using a TimeStamp column in the Entity Framework? Is it possible in a ASP.NET application using the Entity Framework? – Reynier Booysen Aug 24 '12 at 08:58
  • Have a look here. It might help. http://stackoverflow.com/questions/10682591/ef-concurrency-handling-with-timestamp-attribute-in-model-first-approach – tranceporter Oct 03 '12 at 16:05

2 Answers2

3

Optimistic concurrency in EF works fine. Even with stored procedures.

ObjectContext.DeleteObjects passes original values of entity to delete function. This makes sense. Original values are used to identify the row to delete. When you delete object, you don't (usually) have meaningful edits to your entity. What do you expect EF to do with then? Write? To what records?

One legitimate use for passing modified data to delete function is when you want to track deletes in some other table and you need to throw in some information not accessible at database layer, only at business layer. Examples include application level user name or reason to delete. In this situation you need to construct entity with this values as original values. One way to do it:

var x = db.MyTable.Single(k => k.Id == id_to_delete);
x.UserName = logged_in_user;
x.ReasonForChange = some_reason;
// [...]
db.ObjectStateManager.ChangeObjectState(x, EntityState.Unchanged);
db.MyTable.DeleteObject(x);
db.SaveChanges();

Of course, better strategy might be to do it openly in business layer.

I don't understand your use case with rowversion/timestamp.

  1. To avoid concurrency issues you pass original timestamp to modifying code. That way it can be compared to current value in database to detect if record changed since you last read it. Comparing it with new value makes little sense.

  2. You usually use change markers that are automatically updated by database like rowversion/timestamp in SQL Server, rowversion in Oracle or xmin in PostgreSQL. You don't change its value in your code.

  3. Still, if you maintain row version manually, you need to provide:

    a) new version to insert and update to be written, and

    b) old version (read from database) to update and delete to check for concurrent changes.

    You don't send new value to delete. You don't need to. Also, when using stored procedures for modification, it's better to compute new version in the procedure and return it to application, not the other way around.

Tomek Szpakowicz
  • 14,063
  • 3
  • 33
  • 55
0

Hard to tell without seeing any code, but maybe when the postback occurs the page is being re-bound before your delete method is firing? On whatever method databinds the form controls (I assume it's OnLoad or OnInit), have you wrapped any databinding calls with if ( !this.IsPostBack ) { ... }?

Also I'm not sure if there's a reason why you're explicitly storing the concurrency flag in viewstate/session variables, but a better way to do it (imo) is to add the timestamp to the DataKeyNames property of the FormView/GridView (ie: <asp:FormView ID='blah' runat='server' DataKeyNames='Id, Timestamp'>.

This way you don't have to worry about manually storing or updating the timestamp. ;)

Nathon Fowlie
  • 122
  • 7