I have an endpoint that creates a new category, the category entity has a property called CategoryCode, which is unique and is calculated based on the last value in the database.

For simplicity, let’s just assume that I get the max CategoryCode value and increment it.

[HttpPost]
[Route("TestAwait")]
public async Task<ActionResult<GeneralApiResponse<StoreHistoryResponse>>> TestAwait(TestModel testId)
{
    try
    {
        var categoryCode = await _context.Categories.MaxAsync(x => x.CategoryCode);
        Debug.WriteLine($"Category Max Code {categoryCode} From {testId.StoreId}");
        var newCategoryCode = ++categoryCode;

        await Task.Delay(20000); //For Testing

        var newCategory = new Category()
        {
            CategoryName = $"Test {newCategoryCode}",
            CategoryCode = newCategoryCode,
            BranchId = 1
        };

        await _context.Categories.AddAsync(newCategory);
        await _context.SaveChangesAsync();

        return Ok(newCategory);
    }
    catch (Exception ex)
    {
        return BadRequest(ex.InnerException != null ? ex.InnerException.Message : ex.Message);
    }
}

Now the problem that faces me is when multiple clients call this endpoint at the same time, some of them won’t have the latest max value.

So my question here is how can I handle this scenario so that when multiple clients call the end point at the same time, they will all have the latest values in the database?

I’m not sure if this has nothing to do with dependency injection or
if I can make this endpoint await all clients, not just a specific client or there some other solution?