title: Entity Framework autoincrement key
body: <p>I'm facing an issue of duplicated incremental field on a concurrency scenario.</p>
  
  <p>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. </p>
  
  <p>Between the getting of last <code>SequenceNumber</code> and saving, that's where the concurrency is happening.</p>
  
  <p>I'm not using <code>ID</code> for <code>SequenceNumber</code> as it is not a unique constraint, and may reset on certain conditions such as monthly, yearly, etc.</p>
  
  <pre><code>InvoiceNumber       | SequenceNumber | DateCreated
  INV00001_08_14      | 1              | 25/08/2014
  INV00001_08_14      | 1              | 25/08/2014 &lt;= (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 &lt;= (sequence number reset)
  </code></pre>
  
  <p>Invoice number is formatted based on the <code>SequenceNumber</code>.</p>
  
  <p>After some research I've ended up with these possible solutions, but want to know the best practice</p>
  
  <ol>
  <li><p>Pessimistic 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?)</p></li>
  <li><p>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)</p></li>
  </ol>
  
  <p>==============================</p>
  
  <p><strong>EDIT</strong></p>
  
  <p>Another solution that I've thought of is to make the <strong>InvoiceNumber</strong> a <strong>Unique Constraint</strong>, so on cases of duplicate entries it would throw the Unique Constraint Violation errors, catch it, and reattempt to get a new SequenceNumber and InvoiceNumber to reinsert to the DB.</p>