Enhancing ASP.NET Core Applications: Optimistic Concurrency Management with Database-Generated Tokens using Entity Framework

In a my previous article, I have discussed about verious forms of concurrency. As you’re already aware, optimistic concurrency stands out as the prevalent method for managing such scenarios. This article delves into the practical implementation of handling concurrency in ASP.NET using Entity Framework, specifically focusing on utilizing a optimistic concurrency using database-generated token.

Tools and Technology Used

• ASP.net core Web API
• Visual C#
• Entity Framework
• SQL Server

Step 1: Create a asp.net core web api project name ConcurrencyHandling.API

Step 2: Install the following nuget packages in the project.

Microsoft.EntityFrameworkCore
Microsoft.EntityFrameworkCore.Design
Microsoft.EntityFrameworkCore.SqlServer
Microsoft.EntityFrameworkCore.Tools

Step 3: Create a Model class name Account in Models folder

• Account class to store Account Information
• In the following class RowVersion property is used to track the updated version of the record.

Account.cs

using Swashbuckle.AspNetCore.Annotations;
using System.ComponentModel.DataAnnotations;

namespace ConcurrencyHandling.API.Models
{
    public class Account
    {
        public int AccountID { get; set; }

        public string Name { get; set; }

        public decimal Balance { get; set; }

        [Timestamp]
        public byte[] RowVersion { get; set; } = new byte[8];
    }
}

Step 4: Create a Context class name ApplicationDbContext in Data folder.

• The following code block is used in OnModelCreating method to handle concurrency.

   modelBuilder.Entity<Account>()
                .Property(p => p.RowVersion).IsConcurrencyToken();

• IsConcurrencyToken() marks the property as a concurrency token. This is used by EF Core to implement optimistic concurrency. EF Core will use the value of the property in the WHERE clause of UPDATE and DELETE statements.

ApplicationDbContext.cs

using ConcurrencyHandling.API.Models;
using Microsoft.EntityFrameworkCore;

namespace ConcurrencyHandling.API.Data
{
    public class ApplicationDbContext : DbContext
    {

        public virtual DbSet<Account> Accounts { get; set; }


        public ApplicationDbContext(DbContextOptions<ApplicationDbContext> options) : base(options)
        {

        }

        protected override void OnModelCreating(ModelBuilder modelBuilder)
        {
            base.OnModelCreating(modelBuilder);

            // IsConcurrencyToken() marks the property as a concurrency token.
            // This is used by EF Core to implement optimistic concurrency.
            // EF Core will use the value of the property in the WHERE clause of UPDATE and DELETE statements.

            modelBuilder.Entity<Account>()
                .Property(p => p.RowVersion).IsConcurrencyToken();
        }

    }
}

Step 5: Add connection string in appsettings.json file

appsettings.json

{
  "Logging": {
    "LogLevel": {
      "Default": "Information",
      "Microsoft.AspNetCore": "Warning"
    }
  },
  "ConnectionStrings": {
    "DefaultConnection": "Server=localhost;Database=YourPassword;User Id=sa;Password=oLdViCtOrY2008;TrustServerCertificate=True;"
  },
  "AllowedHosts": "*"
}

Step 6: Configure Program class as follows.

Program.cs

using ConcurrencyHandling.API.Data;
using Microsoft.EntityFrameworkCore;

var builder = WebApplication.CreateBuilder(args);

// Add services to the container.

builder.Services.AddControllers();
// Learn more about configuring Swagger/OpenAPI at https://aka.ms/aspnetcore/swashbuckle
builder.Services.AddEndpointsApiExplorer();


builder.Services.AddSwaggerGen();

builder.Services.AddDbContext<ApplicationDbContext>(options =>
    options.UseSqlServer(builder.Configuration.GetConnectionString("DefaultConnection")));


var app = builder.Build();

// Configure the HTTP request pipeline.
if (app.Environment.IsDevelopment())
{
    app.UseSwagger();
    app.UseSwaggerUI();
}

app.UseHttpsRedirection();

app.UseAuthorization();

app.MapControllers();

app.Run();

Step 7: Create a Controller class AccountsController in Controllers folder.

• In the AccountsController class that follows, the statement booking.RecordVersion = Guid.NewGuid(); is employed within the PutBooking and PostBooking methods to update the record version. This updated version will then be utilized in subsequent requests, such as those for updating or deleting a record.

AccountsController.cs

using ConcurrencyHandling.API.Data;
using ConcurrencyHandling.API.Models;
using Microsoft.AspNetCore.Mvc;
using Microsoft.EntityFrameworkCore;

namespace ConcurrencyHandling.API.Controllers
{

    // Concurrency check using database generated token
    [Route("api/[controller]")]
    [ApiController]
    public class AccountsController : ControllerBase
    {
        private readonly ApplicationDbContext _context;

        public AccountsController(ApplicationDbContext context)
        {
            _context = context;
        }

        // GET: api/Accounts
        [HttpGet]
        public async Task<ActionResult<IEnumerable<Account>>> GetAccounts()
        {
            if (_context.Accounts == null)
            {
                return NotFound();
            }
            return await _context.Accounts.ToListAsync();
        }

        // GET: api/Accounts/5
        [HttpGet("{id}")]
        public async Task<ActionResult<Account>> GetAccount(int id)
        {
            if (_context.Accounts == null)
            {
                return NotFound();
            }
            var account = await _context.Accounts.FindAsync(id);

            if (account == null)
            {
                return NotFound();
            }

            return account;
        }

        // PUT: api/Accounts/5
        // To protect from overposting attacks, see https://go.microsoft.com/fwlink/?linkid=2123754
        [HttpPut("{id}")]
        public async Task<IActionResult> PutAccount(int id, Account account)
        {
            if (id != account.AccountID)
            {
                return BadRequest();
            }

            _context.Entry(account).State = EntityState.Modified;

            try
            {
                await _context.SaveChangesAsync();
            }
            catch (DbUpdateConcurrencyException)
            {
                if (!AccountExists(id))
                {
                    return NotFound();
                }
                else
                {
                    throw;
                }
            }

            return NoContent();
        }

        // POST: api/Accounts
        // To protect from overposting attacks, see https://go.microsoft.com/fwlink/?linkid=2123754
        [HttpPost]
        public async Task<ActionResult<Account>> PostAccount(Account account)
        {
            if (_context.Accounts == null)
            {
                return Problem("Entity set 'ApplicationDbContext.Accounts'  is null.");
            }
            _context.Accounts.Add(account);
            await _context.SaveChangesAsync();

            return CreatedAtAction("GetAccount", new { id = account.AccountID }, account);
        }

        // DELETE: api/Accounts/5
        [HttpDelete("{id}")]
        public async Task<IActionResult> DeleteAccount(int id, Account account)
        {
            if (_context.Accounts == null)
            {
                return NotFound();
            }
            //var account = await _context.Accounts.FindAsync(id);
            if (account == null)
            {
                return NotFound();
            }

            _context.Accounts.Remove(account);

            try
            {
                await _context.SaveChangesAsync();
            }
            catch (DbUpdateConcurrencyException)
            {
                if (!AccountExists(id))
                {
                    return NotFound();
                }
                else
                {
                    throw;
                }
            }

            return NoContent();
        }

        private bool AccountExists(int id)
        {
            return (_context.Accounts?.Any(e => e.AccountID == id)).GetValueOrDefault();
        }
    }
}

Step 8: Create migration and update database

• Open Package Manager Console (PMC).
• Select the project name ConcurrencyHandling.API
• Run the following command in PMC

Add-Migration InitialCreate

This will create a new migration file named “InitialCreate” under the Migrations folder of your

• Run the following command in PMC

Update-Database

Step 9: Run the application and test concurrency

• Create a new record by PostAccount action using swagger. Sample example for post data.

{
  "accountID": 0,
  "name": "Mahedee Hasan",
  "balance": 1000
}

• Update the record using the PutAccount action in Swagger. A sample input is provided below. Meanwhile, open another tab and make an update to the record. Your record version will be altered. If you attempt to update the record using the previous record version, it will result in an exception due to concurrency. However, if no one else has updated it and you are using the correct record version, you can update the record without encountering any exceptions.

{
  "accountID": 2,
  "name": "Tahiya Hasan",
  "balance": 1000,
  "rowVersion": "AAAAAAAAB9U="
}

• Delete the record using the DeleteAccount action in Swagger. A sample input is provided below. Meanwhile, open another tab and update the record. Your record version will be changed. If you attempt to delete the record using the previous record version, it will throw an exception due to concurrency. However, if no one else has updated it and you are using the correct record version, you can delete the record without encountering any exceptions.

{
  "accountID": 2,
  "name": "Tahiya Hasan",
  "balance": 1000,
  "rowVersion": "AAAAAAAAB9U="
}

• When we execute update and delete request in the background the following query execute.

UPDATE [Accounts] SET [Balance] = @p0, [Name] = @p1
OUTPUT INSERTED.[RowVersion]
WHERE [AccountID] = @p2 AND [RowVersion] = @p3;

• To view the generated SQL by Entity framework. Configre SQL Log in Program class as follows and you will see the the SQL output in Visual Studio Output window.

builder.Services.AddDbContext<ApplicationDbContext>(options =>
    options.UseSqlServer(builder.Configuration.GetConnectionString("DefaultConnection"))
    .LogTo(Console.WriteLine, LogLevel.Information));

Source code