In this post, we will cover how to handle concurrency for a resource in an ASP.NET Core Web API. The endpoint we will focus on is updating a product resource. The product lives in a SQL Server database and we access it with Dapper. We will handle the case when requests are trying to update the product at the same time.

The code is deliberately simple and focused on the handling of concurrency.

An existing action method

Here’s our existing action method for handling requests to update a product:

[HttpPut()]
public async Task<ActionResult<Product>> Put([FromBody] Product product)
{
  using (var connection = new SqlConnection(_configuration["ConnectionStrings:DefaultConnection"]))
  {
    await connection.OpenAsync();
    var existingProduct = await connection.QueryFirstOrDefaultAsync<Product>("SELECT * FROM Product WHERE ProductId = @ProductId", new { ProductId = product.ProductId });
    if (existingProduct == null)
    {
        return new NotFoundResult();
    }
    await connection.ExecuteAsync(@"UPDATE Product
                                SET ProductName=@ProductName,
                                    UnitPrice=@UnitPrice,
                                    UnitsInStock=@UnitsInStock
                                WHERE ProductId = @ProductId",
                                product);
    return Ok(product);
  }
}

The Product model is as follows:

public class Product
{
  public Guid ProductId { get; set; }
  public string ProductName { get; set; }
  public decimal UnitPrice { get; set; }
  public int UnitsInStock { get; set; }
}

The problem with this is that a request can wipe over changes from another request. This might be fine in some APIs, but some APIs might want to stop this from happening.

We also have an action method to get a product:

[HttpGet("{productId}")]
public async Task<ActionResult<Product>> GetById(Guid productId)
{
  using (var connection = new SqlConnection(_configuration["ConnectionStrings:DefaultConnection"]))
  {
    await connection.OpenAsync();
    var product = await connection.QueryFirstOrDefaultAsync<Product>("SELECT * FROM Product WHERE ProductId = @ProductId", new { ProductId = productId });
    if (product == null) return NotFound();
    return Ok(product);
  }
}

So, an app consuming this API would:

• get the product from the API
• display it on a page
• allow the user to make changes to the product
• submit the updated product to the API

A solution

A solution is to check that the product hasn’t changed between an app getting it and submitting changes to it. If the product isn’t up-to-date, then another user has been making changes at the same time, and the changes can be rejected.

How can we check whether the product has changed though? Well, if the product is persisted in SQL Server, we can use [rowversion](https://docs.microsoft.com/en-us/sql/t-sql/data-types/rowversion-transact-sql). rowversion is a mechanism for automatically version-stamping table rows. If we add a field of type rowversion, SQL Server will automatically change the value of this field every time a change occurs in that row.

If we include the value from this rowversion field in the GET request for a product and require it in the PUT request, we can check if the product has changed before making the database update.

#asp.net #asp.net #.net core #api #dapper