Odtnhj

I have a table of random unique number that they served as account Id for a user.

This scenario will use in Register method of Web API to get first unique Number and attached it into a user and after a successful creation, the fetched unique Number will be removed from database:

 public async Task<ActionResult> Register([FromBody] RegisterDto model)
 

 //get the first unique number from the database
 var UniqueNumber = _context.UniqueNumbers.First();


 var user = new User
 
 UserName = model.Email,
 Email = model.Email,
 PhoneNumber = model.PhoneNumber,
 FirstName = model.FirstName,
 LastName = model.LastName,
 UserProfile = new UserProfile()
 
 AccountNumber = UniqueNumber.Number,
 ,
 ;

 //creating user
 var createResult = await _userManager.CreateAsync(user, model.Password);
 if (!createResult.Succeeded) return BadRequest(new isSucceeded = createResult.Succeeded, errors = createResult.Errors );

 //Delete the fetched UniqueId from the database
 _context.UniqueNumbers.Remove(UniqueNumber);
 _context.SaveChanges();

 return Ok(new
 
 isSucceeded = true
 );
 

My question is how do I prevent collision in multiple calls on API since it may return same unique number for multiple calls?

On EF Core and Microsoft SQL Server you should simply use a SEQUENCE object to generate your keys. See Sequences - EF Core

If really, really want to proceed with your original design you could use FromSql to run a DELETE … OUTPUT, something like:

var UniqueNumber = _context.UniqueNumbers.FromSql("delete top (1) from UniqueNumbers output deleted.*").Single();