EF Core DbUpdateConcurrencyException does not work as expected

Question

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.

Solution

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

Comments

[pantonis]

Anyone can help?

[Gert Arnold]

Asking for help in comments isn't really useful. It doesn't bump the question back to the front page. Editing your question does. For instance to add the code that executes the actual modification. That part is essential for answering the question.

[pantonis]

Added the full code

[pantonis]

Thanks for your help. I want to avoid this solution and use the ConcurrencyCheck attribute

[pantonis]

Thanks for your suggestion. I managed to simulate the ms scenario by adding an if condition waiting for a specific value of balance and then Thread Sleep. After the thread enters a sleep mode I initiate a new thread which updates the Client balance. When the first thread resumes and tries to update the value that it does not exist anymore the DbUpdateConcurrencyException does not fire. I have also a dozen of other properties in the same table that need to be checked for concurrency. not just the balance, just I posted a small sample of my code above.

[pantonis]

As I mentioned in an answer above I want to avoid Rowversion solution and use the ConcurrencyCheck attribute since it is a functionality that the EF core offers. Thanks

[pantonis]

Thanks. Dont know what happened but it seems to work without any issues now without changing anything. My code above seemed correct. Maybe it was a threading issue for testing