c# - Auto-increment a number when DatabaseGenerated.Identity has already been used with Entity Framework -

we have situation using guids our table keys. technically selected guids low chance of collision, , application day distributed, nightly data-sync between locations (hence, need low collision chance id)

however, business wants human readable number can used on-screen, printed repots, labels, , use in conversation. we're using 5-digit number, starting 10000. these numbers can duplicated across locations (location have request 10000, location b too).

our model looks this:

public class request {     [key, databasegenerated(databasegeneratedoption.identity)]     public guid id { get; set; }      public int number { get; set; } } 

then in our save method, we're doing this:

newrequestnumber = (_dbcontext.requests.max(r => r.requestnumber)) + 1;  request.number = newrequestnumber  _dbcontext.requests.add(request);  _dbcontext.savechanges() 

obviously naive, , running issues number duplicated.

as far can tell, can't annotate number property databasegeneratedoptions.idnetity since has been used once. can't find set using fluent api mark field auto-generated number.

my initial thought switch sort of optimistic locking strategy. add unique constraint request number, , try save in try/catch. if doesn't save, grab number again, rinse , repeat. problem if request fails save other reason other unique number constraint, we'll stuck in loop.

i feel i'm missing obvious solution here, ideas?

you create table single column hold integer key , column name. increment needed, add more rows represent different types of surrogate keys.

i wouldn't use identity because require inserting many many rows... update column given row.