Async call to a method which increments a value avoid duplicates

Question

I am try to call a method which creates an ID, from AAA000 -> ZZZ999. The method itself works fine, however I am calling it asynchrously which is causing issues when 2 or more cases are created alongside one another. The highest value is stored in the DB (it doesn't have to be), and I am trying to do this:

Get highest current value -> Increment it using my method -> Save new highest value -> Return new highest value.

Before this method I was just getting the max from the Case table.

I have considered a few ways to achieve this, including seeding the DB from AAA000 -> ZZZ999 and looking up my incremented by postgres ID for the 'friendly' value. However this seems suboptimal.

Any suggestions on how this could be achieved would be appreciated:

Overview of code:

.net core microservices
CQRS
Amazon SQS
MassTransit
AutoMapper
MediatR

CONSUMER

    public async Task Consume(ConsumeContext<EmailClassified> context)
    {
        var caseCommand = new CreateCaseFromEmailCommand(context.Message.Classification, context.Message.AiProbability, context.Message.Email);
        var newCase = await _mediator.Send(caseCommand);

        if (newCase != null)
        {
            // do something signalR
            await Task.CompletedTask;
        }
        else
        {
            // log error
            await Task.CompletedTask;

        }
    }

COMMAND

public class CreateCaseFromEmailCommand : IRequest<Case>
{
    public string Classification { get; set; }
    public decimal AiProbability { get; set; }
    public CaseEmail Email { get; set; }

    public CreateCaseFromEmailCommand(string classification, decimal aiProbability, CaseEmail email)
    {
        Classification = classification;
        AiProbability = aiProbability;
        Email = email;
    }
}

HANDLER

public class CreateCaseFromEmailCommandHandler : IRequestHandler<CreateCaseFromEmailCommand, Case>
{
    private readonly IMyDbContext _context;

    public CreateCaseFromEmailCommandHandler(IMyDbContext context)
    {
        _context = context;
    }

    public async Task<Case> Handle(CreateCaseFromEmailCommand request, CancellationToken cancellationToken)
    {
        try
        {
            NextIdHelper helper = new NextIdHelper(_context);
            string caseReference = helper.GetNextFriendlyId();


            var investor = _context.Set<Investor>()
                .FirstOrDefault(i => i.EmailAddress.ToLower() == request.Email.FromAddress.ToLower());
            var classification =  _context.Set<Classification>().Include(t => t.Team)
                .FirstOrDefault(c => c.Name.ToLower() == request.Classification.ToLower());
            var team = classification.Team;

            var entity = new Case()
            {
                FriendlyCaseId = caseReference,
                Summary = request.Email.Subject,
                Description = request.Email.Message,
                Priority = Priority.Medium,
                CaseStatus = CaseStatus.Open,
                CreatedDate = DateTime.Now,
                NextSla = DateTime.Now.AddDays(1),
                Investor = investor,
                UnknownInvestorEmail = investor == null ? request.Email.FromAddress : null,
                Classification = classification,
                Team = team,
                AiProbability = request.AiProbability,
                Emails = new List<CaseEmail> {request.Email}
            };

            _context.Set<Case>().Add(entity);

            await _context.SaveChangesAsync(cancellationToken);

            return entity;
        }
        catch (Exception ex)
        {
            return null;
        }
    }

HELPER

public class NextIdHelper
{
    private readonly IMyDbContext _context;
    public string Value { get; set; }

    public NextIdHelper(IMyDbContext context)
    {
        _context = context;
    }
    public string GetNextFriendlyId()
    {
        var nextValue = "";
        var highestId = _context.Set<MaxCaseReference>().FirstOrDefault();

        if (!string.IsNullOrWhiteSpace(Value))
        {
            nextValue = CalculateNextId(Value);
        }
        else
        {
            nextValue = CalculateNextId(highestId.Reference);
        }

        highestId.Reference = nextValue;
        Value = nextValue;

        _context.SaveChanges();

        return nextValue;
    }

    private string CalculateNextId(string currentId)
    {
        char[] characters = currentId.ToCharArray();
        int currNum = int.Parse(currentId.Substring(currentId.Length - 3));

        Tuple<char, char, char, int> id = new Tuple<char, char, char, int>(characters[0], characters[1], characters[2], currNum);

        var number = id.Item4 + 1;
        var c3 = id.Item3;
        var c2 = id.Item2;
        var c1 = id.Item1;

        if (number > 999)
        {
            number = 0;
            c3++;
            if (c3 > 'Z')
            {
                c3 = 'A';
                c2++;
                if (c2 > 'Z')
                {
                    c2 = 'A';
                    c1++;
                    if (c1 > 'Z')
                    {
                        throw new IndexOutOfRangeException("Next ID bigger than \"ZZZ999\"");
                    }
                }
            }
        }

        var next = new Tuple<char, char, char, int>(c1, c2, c3, number);

        return $"{next.Item1}{next.Item2}{next.Item3}{next.Item4.ToString().PadLeft(3, '0')}";
    }
}

Answer 1

Instead of selecting and incrementing the string, generate an ID with a SEQUENCE or IDENTITY column, and if you must, translate it to a string on the client. EG

static string GetId(int id)
{
    var chars = new char[6];
    char Zero = '0';
    chars[5] = (char) (Zero + id % 10);
    id = id / 10;
    chars[4] = (char)(Zero + id % 10);
    id = id / 10;
    chars[3] = (char)(Zero + id % 10);
    id = id / 10;
    char A = 'A';
    chars[2] = (char)(A + id % 26);
    id = id / 26;
    chars[1] = (char)(A + id % 26);
    id = id / 26;
    chars[0] = (char)(A + id % 26);

    return new string(chars);
}

to get the sequence value add a method to your DbContext like this:

    private long GetSequenceNextVal(string sequenceName)
    {
        var con = Database.GetDbConnection();
        var initialState = con.State;
        if (con.State != System.Data.ConnectionState.Open)
            con.Open();  //the DbContext will clean up after exceptions

        var cmd = con.CreateCommand();

        var connectionType = con.GetType().Name;

        string sql;
        if (connectionType == "SqlConnection")
        {
            sql = $"select next value for {sequenceName};";
        }
        else if (connectionType == "NpgsqlConnection")
        {
            sql = $"SELECT nextval('{sequenceName}');";
        }
        else if (connectionType == "OracleConnection")
        {
            sql = $"select {sequenceName}.nextval from dual";
        }
        else
        {
            throw new NotImplementedException($"No sequence generatator for {connectionType}");
        }

        cmd.CommandText = sql;
        var result = cmd.ExecuteScalar();
        var val = Convert.ToInt64(result);

        if (initialState == System.Data.ConnectionState.Closed)
            con.Close();  

        return val;
    }