"RowsAffectedParameter throws OptimisticConcurrencyException if zero is returned" Can't be serious, surely?

Question

From How to: Map Modification Functions to Stored Procedures (Entity Data Model Tools):

For integer-valued output parameters, the Rows Affected Parameter checkbox is enabled. If the checkbox is selected for a parameter and the value returned is zero when the insert operation is called, an OptimisticConcurrencyException will be thrown.

Why on earth does this happen? Zero might be a perfectly valid value for an output parameter - why would I want it to throw an error?

Plesae can someone explain this decision?

---

http://sqlblog.com/blogs/jamie_thomson/ | @jamiet | About me

Answer 1

 

Hi Jamie,

Thanks for your post.

Let's see what happens in SaveChanges() through Reflector, The Update method will be called.

internal static int Update(IEntityStateManager stateManager, IEntityAdapter adapter)
{
  IntPtr ptr;
  int num3;
  EntityBid.ScopeEnter(out ptr, "<upd.Internal.UpdateTranslator.Update>");
  EntityConnection connection = (EntityConnection) adapter.Connection;
  MetadataWorkspace metadataWorkspace = connection.GetMetadataWorkspace();
  int? commandTimeout = adapter.CommandTimeout;
  try
  {
    UpdateTranslator translator = new UpdateTranslator(stateManager, metadataWorkspace, connection, commandTimeout);
    Dictionary<int, object> identifierValues = new Dictionary<int, object>();
    List<KeyValuePair<PropagatorResult, object>> generatedValues = new List<KeyValuePair<PropagatorResult, object>>();
    IEnumerable<UpdateCommand> enumerable = translator.ProduceCommands();
    UpdateCommand source = null;
    try
    {
      foreach (UpdateCommand command2 in enumerable)
      {
        source = command2;
        <strong><span style="text-decoration:underline">long rowsAffected = command2.Execute(translator, connection, identifierValues, generatedValues);
        translator.ValidateRowsAffected(rowsAffected, source);
</span></strong>      }
    }
    catch (Exception exception)
    {
      if (RequiresContext(exception))
      {
        throw EntityUtil.Update(Strings.Update_GeneralExecutionException, exception, translator.DetermineStateEntriesFromSource(source));
      }
      throw;
    }
    translator.BackPropagateServerGen(generatedValues);
    num3 = translator.AcceptChanges(adapter);
  }
  finally
  {
    EntityBid.ScopeLeave(ref ptr);
  }
  return num3;
}

Now we look into ValidateRowsAffected method

private void ValidateRowsAffected(long rowsAffected, UpdateCommand source)
{
  if (0L == rowsAffected)
  {
    IEnumerable<IEntityStateEntry> stateEntries = this.DetermineStateEntriesFromSource(source);
    throw EntityUtil.UpdateConcurrency(rowsAffected, null, stateEntries);
  }
}

You can see here if rowsAffected==0, the exception will throw.

Have a nice day.

---

Alan Chen[MSFT]
MSDN Community Support | Feedback to us
Get or Request Code Sample from Microsoft
Please remember to mark the replies as answers if they help and unmark them if they provide no help.

Answer 2

Hi Jamie,

 I am writing to check the status of the issue on your side.  Would you mind letting us know the result of the suggestions? 
 
If you need further assistance, please feel free to let me know.   I will be more than happy to be of assistance.

Have a nice day.

---

Alan Chen[MSFT]
MSDN Community Support | Feedback to us
Get or Request Code Sample from Microsoft
Please remember to mark the replies as answers if they help and unmark them if they provide no help.

Answer 3

Hello Alan,

What suggestion? You confirmed that the documentation is correct but that didn't answer my question which is "why does it behave this way?"

 

Jamie

---

http://sqlblog.com/blogs/jamie_thomson/ | @jamiet | About me

Answer 4

Hi Jamie,

Sorry for the delay.   A very senior expert gave me some suggestions about this problem:

"This basic design has been in place since ADO.NET.  When you update disconnected data, you might want to know if another user has updated the your data between the time you read it and the time you’re writing it back.   The mechanism we use for that is the OptimisticConcurrencyException.  If another user has changed the data, the update fails with an OptimisticConcurrencyException.  Typically in response you would reload the current data, inform the user that their update failed because it would have overwritten another user’s changes and allow them to modify the current data.

Now the way we typically detect optimistic concurrency violations is that the update query will be modified so that it affects zero rows if another user has made a conflicting change to the row.  Commonly this will be a query like

UPDATE T SET A=@A, B=@B
WHERE ID=@ID and Timestamp=@TS

So if another user has changed the Timestamp column since we read the row, the update will not affect any rows.  Then from the client you detect optimistic concurrency violations by checking the rows affected by the update statement.  If it affects 1 row, then the update succeeded.  If it affects 0 rows, then an optimistic concurrency violation occurred.  There’s no SqlException here, since the DML simply affected 0 rows, so EF throws an OptimisticConcurrencyException to inform the program that the update failed.

For stored procedures the records affected mechanism is sometimes tricky, since you have to carefully turn NOCOUNT on and off in the body of the procedure to get the right rowcount.  As an alternative you can mark an output parameter as the “Rows Affected Parameter”, and EF will use this value instead of the row count reported by database to detect optimistic concurrency violations."

Hope you have a nice day!

Thanks

---

Michael Sun [MSFT]
MSDN Community Support | Feedback to us
Get or Request Code Sample from Microsoft
Please remember to mark the replies as answers if they help and unmark them if they provide no help.

Answer 5

Hello,

Its taken me a long time to reply but I want to thank you for posting such a detailed response - clearly optimistic concurrency is something I need to learn more about.

Thanks again - Microsoft have gone up in my estimation a tiny bit today.

 

Regards
Jamie

---

http://sqlblog.com/blogs/jamie_thomson/ | @jamiet | About me

Answer 6

Hello,

Its taken me a long time to reply but I want to thank you for posting such a detailed response - clearly optimistic concurrency is something I need to learn more about.

Thanks again - Microsoft have gone up in my estimation a tiny bit today.

 

Regards
Jamie

---

http://sqlblog.com/blogs/jamie_thomson/ | @jamiet | About me

It's great to hear you say so, Jamie.  Even it's "a tiny bit". :)   Please believe we Microsoft are always doing the best! 

Hope you have nice day!

Thanks

---

Michael Sun [MSFT]
MSDN Community Support | Feedback to us
Get or Request Code Sample from Microsoft