How to generate STRICTLY increasing number with no gap in Azure Sql Database

Question

I have a MVC 5 application that access a Sql Database through Entity framework. I have a invoice model that looks like :

class Invoice 
{
public int Id {get; set;}
public int InvoiceNumber {get; set;}
// other fields ....
}

InvoiceNumber have a lot of legal constraint. It needs to be unique, and incremental with no gap. I initially though that my Id would do the job till I have found that for some reasons I may have gaps between the id ( 1,2,3,...,7 then 1000 ... , for inst see Windows Azure SQL Database - Identity Auto increment column skips values ). Take into account that many clients could be connected at the same time so these Inserts may be concurrent.

Does someone know how to force EF or the database to generate this kind of invoice number.

Answer 1

I think the only way that you're going to get this exact functionality is to drop the identity, add a unique index on the column, then design a very lightweight process that issues a call to the table to insert the next value such as

INSERT INTO Invoice (InvoiceID)
OUTPUT inserted.InvoiceID
SELECT MAX(InvoiceID) + 1
FROM Invoice

You would then write back an update containing the whole value, or alternatively set this up as a one and done type of query where you write the value back as one transaction in a statement such as when you pass all intended inserts into a procedure to write the value out.

I don't know about doing this in entity framework specifically as I don't do much application development, but it is possible to accomplish this just takes longer to do manually rather than trusting a table setting.

I am assuming here of course that you have already investigated the NOCACHE solution presented in the other question and found some sort of issue with it?

Answer 2

old question, but you could generate a table of all invoice numbers beforehand and mark them as used. then query for the minimum unmarked value.

table doesn't have to be all numbers either, just like 1000 ahead, or whatever's comfortable.