5

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.

Community
  • 1
  • 1
Dave
  • 1,835
  • 4
  • 26
  • 44
  • Very interesting question and useful item to be aware of for future projects. Thanks for bringing this up. – David Dec 06 '13 at 15:19

2 Answers2

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?

David
  • 1,591
  • 1
  • 10
  • 22
  • It does not look very ACID. In my case this is a regulatory issue that these numbers to be incremented by 1 exactly and consistant of course. – Dave Dec 06 '13 at 15:46
  • @Dave This answer relies on using the built in isolation levels in SQL Server. If you find that you are seeing failure for duplicate values, ensure that you are not using any nolock or read uncommitted hints and if necessary implement query/table hints to issue more severe locking on the row with the maximum ID to ensure successive queries wait in line to read and commit a new InvoiceID. – David Dec 06 '13 at 15:51
  • @Dave: it is getting way out of my current knowledge of what I can do on Azure Sql Database . I tried initially to have an extra table (key/value store) , lock it , increment it , unlock . But i found it not very good (specially that I may sometimes add many invoice a the same time. – Dave Dec 06 '13 at 15:59
  • @Dave Yeah we're getting to the point where I would need to research and fiddle with it myself. – David Dec 06 '13 at 16:01
1

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.

eric
  • 11
  • 1