Auto-increment Id based on composite primary key

Question

Note: Using Sql Azure & Entity Framework 6

Say I have the following table of a store's invoices (there are multiple stores in the DB)...

CREATE TABLE [dbo].[Invoice] (
    [InvoiceId] INTEGER NOT NULL,
    [StoreId] UNIQUEIDENTIFIER NOT NULL,

    CONSTRAINT [PK_Invoice] PRIMARY KEY CLUSTERED ([InvoiceId] ASC, [StoreId] ASC)
);

Ideally, I would like the InvoiceId to increment consecutively for each StoreId rather than independent of each store...

InvoiceId | StoreId
-------------------
1         | 'A'
2         | 'A'
3         | 'A'
1         | 'B'
2         | 'B'

Question: What is the best way to get the [InvoiceId] to increment based on the [StoreId]?

Possible options:

a) Ideally a [InvoiceId] INTEGER NOT NULL IDENTITY_BASED_ON([StoreId]) parameter of some kind would be really helpful, but I doubt this exists...

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)

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)
);

c) A way to handle this in Entity Framework (code first w/o migration) using DbContext.SaveChangesAsync override or by setting a custom insert query?

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.

Answer 1

After playing around with the answer provided by @Jamiec in my solution I instead decided to go the TRIGGER route in order to persist the invoice number and better work with Entity Framework. Additionally, since ROW_NUMBER doesn't work in an INSERT (AFAIK) I am instead using MAX([InvoiceNumber])+1.

CREATE TABLE [dbo].[Invoice] (
    [InvoiceId] INTEGER NOT NULL,
    [StoreId] UNIQUEIDENTIFIER NOT NULL,
    [InvoiceNumber] INTEGER NOT NULL,

    CONSTRAINT [PK_Invoice] PRIMARY KEY CLUSTERED ([InvoiceId] ASC)
);

CREATE TRIGGER TGR_InvoiceNumber 
    ON [Invoice]
INSTEAD OF INSERT
AS
BEGIN
  INSERT INTO [Invoice] ([InvoiceId], [StoreId], [InvoiceNumber])
       SELECT [InvoiceId], 
              [StoreId], 
              ISNULL((SELECT MAX([InvoiceNumber]) + 1 FROM [Invoice] AS inv2 WHERE inv2.[StoreId] = inv1.[StoreId]), 1)
       FROM inserted as inv1;
END;

This allows me to set up my EF class like:

public class Invoice
{
    [Key]
    [DatabaseGenerated(DatabaseGeneratedOption.Identity)]
    public int InvoiceId { get; set; }

    public Guid StoreId { get; set; }

    [DatabaseGenerated(DatabaseGeneratedOption.Computed)]
    public int InvoiceNumber { get; set; }
}

Answer 2

You should stick to an auto-incrementing integer primary key, this is much simpler than dealing with a composite primary key especially when relating things back to an Invoice.

In order to generate an InvoiceNumber for the sake of a user, which increments per-store, you can use a ROW_NUMBER function partitioned by StoreId and ordered by your auto-incrementing primary key.

This is demonstrated with the example below:

WITH TestData(InvoiceId, StoreId) AS
(
    SELECT 1,'A'
    UNION SELECT 2,'A'
    UNION SELECT 3,'A'
    UNION SELECT 4,'B'
    UNION SELECT 5,'B'
)
Select InvoiceId,
        StoreId,
        ROW_NUMBER() OVER(PARTITION BY StoreId ORDER BY InvoiceId) AS InvoiceNumber
FROM TestData

Result:

InvoiceId | StoreId | InvoiceNumber
1         | A       | 1  
2         | A       | 2  
3         | A       | 3  
4         | B       | 1  
5         | B       | 2