EF eqivalent for rows affected of SqlCommand.Execu

问题:

In an approval workflow I want to ensure that reminder emails are sent exactly once.

With SqlCommand.ExecuteNonQuery I can ensure this by testing the return value. What is the recommended solution using EF? According to the documentation ObjectContext.SaveChanges does not return an equivalent value.

SqlCommand example: (The TransactionScope is used to rollback the DB update in case SendMail fails.)

Dim sql = "UPDATE LeaveApprovalRequests SET State = 'Reminded'" &
          " WHERE ID=3 AND State <>'Reminded'"
Using scope As New TransactionScope
    Using cnx As New SqlConnection(My.Settings.connectionString)
        cnx.Open()
        Dim cmd As New SqlCommand(sql, cnx)
        If 1 = cmd.ExecuteNonQuery Then
             SendMail()
        End If
        scope.Complete()
    End Using
End Using

By enabling optimistic concurrency (using ConcurrencyMode=Fixed on a RowVersion property) and catching the OptimisticConcurrencyException I am able to identify if the object was actually updated in the store. Now the TransactionScope (used to rollback the DB update if SendMail fails) throws a deadlock error. Why?

Using scope As New TransactionScope
  Using ctx As New ApprovalEntities
    Try
      Dim approval = ctx.LeaveApprovalRequests.
        Where(Function(r) r.ID = 3 And r.State = "Created"
        ).FirstOrDefault
      If approval Is Nothing Then
        Console.WriteLine("not found")
        Exit Sub
      End If
      Threading.Thread.Sleep(4000)
      approval.State = "Reminded"
      ctx.SaveChanges()
      SendMail()
    Catch ex As OptimisticConcurrencyException
      Exit Try
    End Try
  End Using
  scope.Complete()
End Using

回答1:

Well, as a matter of fact the exact number of rows affected can be inferred from a call to ObjectContext.SaveChanges().

If you take a look at the ObjectContext.SaveChanges documentation you'll see:

public int SaveChanges()

1. Return Value: The number of objects in an Added, Modified, or Deleted state when SaveChanges was called.
2. "SaveChanges operates within a transaction. SaveChanges will roll back that transaction and throw an exception if any of the dirty ObjectStateEntry objects cannot be persisted."

(1) and (2) basically means that if your call to SaveChanges() has been successfully completed and you don't get any exception then EF guarantees that the return value exactly reflects the number of objects that has been modified.

Therefore, all you need to do is:

try {
    // Try to save changes, which may cause a conflict.
    int num = context.SaveChanges();
    SendMail();
}
catch (OptimisticConcurrencyException) {
    //Refresh the entity, using ClientWins
    context.Refresh(RefreshMode.ClientWins, yourEntityObject);
    //SaveChanges again;
    context.SaveChanges();
}

When Refresh with ClientWins is called, it executes a query to retrieve the current values of this entity in the database, including the new timestamp. Therefore, all of the original field values have been updated to reflect the latest database values so we can safely try SaveChanges() one more time.

Updated With Your Question:
The task is: only send an email if I am able to change the state from created to reminded. Thus it does not make sense to force through the SaveChanges when handling the OptimisticConcurrencyException. The error handler should exit if changing the state caused the exception and otherwise retry the whole task (reading and saving). How can I do this if optimistic concurreny is enabled via a RowVersion column and not by state only?

Well, each time a row is modified, the rowversion field is automatically updated, so in your case that would the best if you turn off Concurrency Mode on rowversion and turn it on, for the State property, so that your code will be as simple as:

try {
    context.SaveChanges();
    SendMail();
}
catch (OptimisticConcurrencyException) {
    // RowVersion Concurrency Mode = Fixed 
    // State Concurrency Mode = None
    // If it reaches here it means that the State has been changed;
    // so you do nothing except than throwing the exception
    throw;
}

But, if you would like to set Concurrency Mode = Fixed only for the rowversion property (like you mentioned), then it means that you can potentially get OptimisticConcurrencyException for change on any field including State, so it will be a little bit more work do the job:

try {
    ctx.SaveChanges();
    SendMail;
}
catch (OptimisticConcurrencyException) {
    // RowVersion Concurrency Mode = Fixed 
    // State Concurrency Mode = None
    // If it reches here it means that ANY/All field(s) has changed
    // So we need to see if it was State:
    ctx.Refresh(RefreshMode.ClientWins, approval);
    ObjectStateEntry ose = ctx.ObjectStateManager.GetObjectStateEntry(approval);
    string stateValue = ose.OriginalValues["State"].ToString();
    // If the value is still "Created" then something else should have changed,
    // And caused the exception, so we can proceed with the Save:
    if (stateValue == "Created") {
        ctx.SaveChanges();
        SendMail;
    }
    else {
        // Nope, it was state, so we throw the exception to the caller:
        throw;
    }

回答2:

As a result of the discussion with Morteza I answer my question as follows.

SaveChanges returns the number of objects it intends to update, not the number it did update in the store. Thus it must be used together with OptimisticConcurrencyException to determine if the change succeeded. One must consider that other properties than the one intended to change can cause a OptimisticConcurrencyException.

Reading an entity and updating it in the same TransactionScope causes a deadlock.

For my Task "Only send an email if I am able to change the State from created to reminded" I use the following solution:

Split the ApprovalRequest entity in two with a 1:1 association, exit on OptimisticConcurrencyException, send mail in TransactionScope with SaveChanges.

ApprovalRequests
  ID (PK)
  RequestedBy
  ...
  RowVersion (ConcurrencyMode=Fixed)

ApprovalRequestStates
  ApprovalRequest_ID (PK, FK)
  State (ConcurrencyMode=Fixed)

Using ctx As New ApprovalEntities
    Dim approval = cxt.ApprovalRequests.Where ...
    Dim state = ctx.ApprovalRequestStates.
        Where(Function(r) r.ApprovalRequest_ID = approval.ID And r.State = "Created"
        ).FirstOrDefault()
    If state Is Nothing Then Exit Sub
    state.State = "Reminded"
    Threading.Thread.Sleep(3000) 
    Using scope As New TransactionScope
        Try
            ctx.SaveChanges()
            SendMail()
            scope.Complete()
        Catch ex As OptimisticConcurrencyException
            Exit Try
        End Try
    End Using
End Using

Beware! Updating a child entity when referencing it via its parent causes a DB update of the parent too - in this case throwing an unwanted OptimisticConcurrencyException. Thus I did not use: ApprovalRequests.ApprovalRequestStates.State = "Reminded"