CODEWITHSUNDEEP
entity-framework
Jon
Jon

Reputation: 655

Entity Framework Optimistic Concurrency Exception with Multiple records updated

When updating multiple records in the database via the entity framework database context, one record is throwing an optimistic concurrency exception and that record is not updated in the database. But I am finding the other records updated are persisted to the database. Should the optimistic concurrency exception not roll back all changes made prior to the optimistic concurrency exception?

Upvotes: 1

Views: 1624

Answers (1)

petesramek
petesramek

Reputation: 3198

I bet exception is thrown by EF library whenever update was expected, but SQL Server response indicates none was made.

Exception thrown by DbContext when it was expected that SaveChanges for an entity would result in a database update but in fact no rows in the database were affected. This usually indicates that the database has been concurrently updated such that a concurrency token that was expected to match did not actually match. Note that state entries referenced by this exception are not serialized due to security and accesses to the state entries after serialization will return null.

Also Entity Framework translates into single SQL UPDATE statement per object e.g.

exec sp_executesql N'SET NOCOUNT ON;

UPDATE [Students] SET [Name] = @p0
WHERE [StudentId] = @p1;
SELECT @@ROWCOUNT;

UPDATE [Students] SET [Name] = @p2
WHERE [StudentId] = @p3;
SELECT @@ROWCOUNT;

UPDATE [Students] SET [Name] = @p4
WHERE [StudentId] = @p5;
SELECT @@ROWCOUNT;

',N'@p1 int,@p0 nvarchar(4000),@p3 int,@p2 nvarchar(4000),@p5 int,@p4 
nvarchar(4000)',
@p1=1,@p0=N'Bill',@p3=2,@p2=N'Steve',@p5=3,@p4=N'James'

go

Which means everything is okay by SQL Server and transaction is commited by Entity Framework. Only EF throws exception to let you know something went wrong. In your case one of those statements updates nothing and number of updated rows was/is 0.

If you need to rollback all changes during such an update you have to encapsulate it with another transaction. Commit and rollback as needed.

using (var dbContextTransaction = context.Database.BeginTransaction()) 
{ 
   try 
   { 
      // Do your stuff

      context.SaveChanges(); 

      dbContextTransaction.Commit(); 
   } 
   catch (DbUpdateConcurrencyException) 
   { 
      // Expected
      dbContextTransaction.Rollback(); 
   }
   catch (Exception)
   {
      // Unexpected
      dbContextTransaction.Rollback();
   }
}

Relevant reading:

More about DbUpdateConcurrencyException Class

More about Update Data in Disconnected Scenario in Entity Framework Core (Update Multiple Entities)

More about Entity Framework Working with Transactions

Upvotes: 1

Related Questions