I need a sequential reference number for items. Normally I would just use an Identity column, but as of SQL 2012 they can jump up to the next 1000 (I'm seeing 1, 2, 1001, 2001, 2002).
Reading up I see suggestions of using a sequence as it's now easy in EF Core 2.1. Problem is it still has some caching, 50 numbers at a time, so I am now seeing 1, 2, 3, 51, 52.
Looking at the SQL documentation I can see you can disable this caching, there is just no way, that I can see, to do it via the fluent API in EF Core.
Suggestions?
I'm curious, so I am looking for a more elegant solution but in the meantime ... Perhaps you can get around this "sideways". Create a table just for this sequencing issue like so:
public class mySequence {
public int sequence {get; set;}
public bool locked {get; set;}
}
Create an index (descending) on sequence. 1) Lock sequence TABLE; 2) Find "next" unlocked sequence #; 3) If none, seed with next X sequence numbers; 4) Lock the "next" sequence #; 5) Unlock Table; 6) Use sequence # in your real data
Have to have exclusive control over some resource to mitigate collisions/duplicates.
Hm, more "elegant" solution presents itself...