title: Auto-increment Id based on composite primary key
body: <p><em>Note: Using Sql Azure &amp; Entity Framework 6</em></p>
  
  <p>Say I have the following table of a store's invoices (there are multiple stores in the DB)...</p>
  
  <pre><code>CREATE TABLE [dbo].[Invoice] (
      [InvoiceId] INTEGER NOT NULL,
      [StoreId] UNIQUEIDENTIFIER NOT NULL,
  
      CONSTRAINT [PK_Invoice] PRIMARY KEY CLUSTERED ([InvoiceId] ASC, [StoreId] ASC)
  );
  </code></pre>
  
  <p>Ideally, I would like the InvoiceId to increment consecutively for each StoreId rather than independent of each store...</p>
  
  <pre><code>InvoiceId | StoreId
  -------------------
  1         | 'A'
  2         | 'A'
  3         | 'A'
  1         | 'B'
  2         | 'B'
  </code></pre>
  
  <p><strong>Question:</strong> What is the best way to get the <code>[InvoiceId]</code> to increment based on the <code>[StoreId]</code>?</p>
  
  <p>Possible options:</p>
  
  <p>a) Ideally a <code>[InvoiceId] INTEGER NOT NULL IDENTITY_BASED_ON([StoreId])</code> parameter of some kind would be really helpful, but I doubt this exists...</p>
  
  <p>b) A way to set the default from the return of a function based on another column? (AFAIK, you can't reference another column in a default)</p>
  
  <pre><code>CREATE FUNCTION [dbo].[NextInvoiceId]
  (
      @storeId UNIQUEIDENTIFIER
  )
  RETURNS INT
  AS
  BEGIN
      DECLARE @nextId INT;
      SELECT @nextId = MAX([InvoiceId])+1 FROM [Invoice] WHERE [StoreId] = @storeId;
      IF (@nextId IS NULL)
          RETURN 1;
  
      RETURN @nextId;
  END
  
  CREATE TABLE [dbo].[Invoice] (
      [InvoiceId] INTEGER NOT NULL DEFAULT NextInvoiceId([StoreId]),
      [StoreId] UNIQUEIDENTIFIER NOT NULL,
  
      CONSTRAINT [PK_Invoice] PRIMARY KEY CLUSTERED ([InvoiceId] ASC, [StoreId] ASC)
  );
  </code></pre>
  
  <p>c) A way to handle this in Entity Framework (code first w/o migration) using <code>DbContext.SaveChangesAsync</code> override or by setting a custom insert query?</p>
  
  <p><em>Note: I realize I could do it with a stored procedure to insert the invoice, but I'd prefer avoid that unless its the only option.</em></p>