Entity Framework autoincrement key
- by Tommy Ong
I'm facing an issue of duplicated incremental field on a concurrency scenario.
I'm using EF as the ORM tool, attempting to insert an entity with a field that acts as a incremental INT field. Basically this field is called "SequenceNumber", where each new record before insert, will read the database using MAX to get the last SequenceNumber, append +1 to it, and saves the changes.
Between the getting of last SequenceNumber and Saving, that's where the concurrency is happening.
I'm not using ID for SequenceNumber as it is not a unique constraint, and may reset on certain conditions such as monthly, yearly, etc.
InvoiceNumber | SequenceNumber | DateCreated
INV00001_08_14 | 1 | 25/08/2014
INV00001_08_14 | 1 | 25/08/2014 <= (concurrency is creating two SeqNo 1)
INV00002_08_14 | 2 | 25/08/2014
INV00003_08_14 | 3 | 26/08/2014
INV00004_08_14 | 4 | 27/08/2014
INV00005_08_14 | 5 | 29/08/2014
INV00001_09_14 | 1 | 01/09/2014 <= (sequence number reset)
Invoice number is formatted based on the SequenceNumber.
After some research I've ended up with these possible solutions, but wanna know the best practice
1) Optimistic Concurrency, locking the table from any reads until the current transaction is completed (not fancy of this idea as I guess performance will be of a great impact?)
2) Create a Stored Procedure solely for this purpose, does select and insert on a single statement as such concurrency is at minimum (would prefer a EF based approach if possible)
