Multiple Wpf clients + SQL Server database concurrency issue

Question

I have a WPF application working on multiple client PCs with EF Core connecting to a single SQL Server.

When I retrieve transaction number, I count the transaction table records and add one to it.

My issue is when two clients request transaction number at the same time, they get the same transaction number.

Any help regarding this issue well be much appreciated.

public async Task<Response<PassportOutputModel>> CreateAsync(PassportInputModel model)
{
    if (!await _validation.IsCorrectAsync(model, EnumPermission.AddPassport))
        return _validation.Response; 

    var number = await UnitOfWork.Passports.GetLastPassportNumberAsync();  

    var passport = Mapper.Map<Passport>(model);

    passport.Number = number;

    await UnitOfWork.Passports.AddAsync(passport);
    await UnitOfWork.CompleteAsync();

    return Response.SuccessCreate(Mapper.Map<PassportOutputModel>(passport));
}

Answer 1

A number of fields are entered at the same time without duplicating, gaping, or jumping.

1- Trigger to automatically add One Number after each Insert process .

2- Indexing number to avoid duplicating and make the process smooth and fast.]

14_Record_In_Same_Time