EF Core DbUpdateConcurrencyException does not work as expected

user_1173307 0
Viewed 11346

I have the following code that I am trying to update ClientAccount using ef core but they Concurrency Check fails:

    public class ClientAccount
    {
        [Key]
        [DatabaseGenerated(DatabaseGeneratedOption.Identity)]
        public long Id { get; set; }

        [Required]
        [ConcurrencyCheck]
        public double Balance { get; set; }

        [Required]
        public DateTime DateTimeCreated { get; set; }

        [Required]
        public DateTime DateTimeUpdated { get; set; }       
    }

    public class ClientRepository
    {
        private readonly MyContext context;

        public ClientRepository(MyContext context)
        {
            this.context = context;
        }

        public ClientAccount GetClientAccount()
        {
            return (from client in context.ClientAccount
                    select client).SingleOrDefault();
        }

        public void Update(ClientAccount client)
        {
            context.Update(client);
            context.Entry(client).Property(x => x.DateTimeCreated).IsModified = false;
        }
    }

    public class ClientService
    {
        private readonly ClientRepository clientRepository;
        private readonly IUnitOfWork unitOfWork;

        public ClientService(ClientRepository clientRepository,
            IUnitOfWork unitOfWork)
        {
            this.unitOfWork = unitOfWork;
            this.clientRepository = clientRepository;
        }

        public void Update(ClientAccount clientAccount)
        {
            if (clientAccount == null)
                return;

            try
            {
                ClientAccount existingClient = clientRepository.GetClientAccount();
                if (existingClient == null)
                {
                    // COde to create client
                }
                else
                {
                    existingClient.AvailableFunds = clientAccount.Balance;
                    existingClient.DateTimeUpdated = DateTime.UtcNow;

                    clientRepository.Update(existingClient);
                }

                unitOfWork.Commit();
            }
            catch (DbUpdateConcurrencyException ex)
            {

            }
        }
    }

Problem is that DbUpdateConcurrencyException is not fired whenever two threads are trying to update it at the same time and thus I don't have expected functionality. I don't understand what is the problem here as marking the property with ConcurrencyCheck attribute should do the work.

ef-core-2.0 entity-framework-core ef-code-first .net c#
2 Answers

does not work as expected

Sure it does, but your code will hardly ever give rise to concurrency exceptions.

In the Update method an existing client is pulled from the database, modified and immediately saved. When coming freshly from the database, the client (obviously) has the latest value of Balance, not the value it had when it entered the UI. The whole operation is a question of milliseconds, small chance that other users save the same client in that short time span.

How to fix it

If you want concurrency conflicts to show up you should store the original value in the ClientAccount object and assign it to the original value in the context. For example like so:

The class:

public class ClientAccount
{
    [Key]
    [DatabaseGenerated(DatabaseGeneratedOption.Identity)]
    public long Id { get; set; }

    [Required]
    [ConcurrencyCheck]
    public double Balance { get; set; }
    
    [NotMapped]
    public double OriginalBalance { get; set; }
    
    ...
}

In the update method, for brevity pretending we have the context available there:

ClientAccount existingClient = db.ClientAccount.Find(clientAccount.Id);

db.Entry(existingClient).OriginalValues["Balance"] = clientAccount.OriginalBalance;

existingClient.Balance = clientAccount.Balance; // assuming that AvailableFunds is a typo
db.SaveChanges();

You also need to set OriginalBalance in the object that is edited by the user. And since you work with repositories you have to add a method that will feed original values to the wrapped context.

A better way?

Now all this was for only one property. It is more common to use one special property for optimistic concurrency control, a "version" property --or field in the database. Some databases (among which Sql Server) auto-increment this version field on each update, which means that it will always be different when any value of a record has been updated.

So let you class have this property:

public byte[] Rowversion { get; set; }

And the mapping:

modelBuilder.Entity<ClientAccount>().Property(c => c.Rowversion).IsRowVersion();

(or use the [System.ComponentModel.DataAnnotations.Timestamp] attribute).

Now instead of storing the original balance and using it later, you can simply do ...

db.Entry(existingClient).OriginalValues["Rowversion"] = clientAccount.Rowversion;

... and users will be made aware of any concurrency conflict.

You can read more on concurrency control in EF-core here, but note that (surprisingly) they incorrectly use IsConcurrencyToken() instead of IsRowVersion. This causes different behavior as I described here for EF6, but it still holds for EF-core.

Sample code

using (var db = new MyContext(connectionString))
{
    var editedClientAccount = db.ClientAccounts.FirstOrDefault();
    editedClientAccount.OrgBalance = editedClientAccount.Balance;
    // Mimic editing in UI:
    editedClientAccount.Balance = DateTime.Now.Ticks;

    // Mimic concurrent update.
    Thread.Sleep(200);
    using (var db2 = new MyContext(connectionString))
    {
        db2.ClientAccounts.First().Balance = DateTime.Now.Ticks;
        db2.SaveChanges();
    }
    Thread.Sleep(200);
    
    // Mimic return from UI:
    var existingClient = db.ClientAccounts.Find(editedClientAccount.ID);
    db.Entry(existingClient).OriginalValues["Balance"] = editedClientAccount.OrgBalance;
    existingClient.Balance = editedClientAccount.Balance;
            
    db.SaveChanges(); // Throws the DbUpdateConcurrencyException
}

This is the executed SQL for the last update:

exec sp_executesql N'SET NOCOUNT ON;
UPDATE [ClientAccount] SET [Balance] = @p0
WHERE [ID] = @p1 AND [Balance] = @p2;
SELECT @@ROWCOUNT;

',N'@p1 int,@p0 float,@p2 float',@p1=6,@p0=636473049969058940,@p2=1234
user_8617160
user_64152140
Related