Locking a table during 2 statements in Entity Framework

Question

I have the following statements:

int newId = db.OnlinePayments.Max(op => op.InvoiceNumber) + 1;
opi.InvoiceNumber = newId;
await db.SaveChangesAsync();

The InvoiceNumber column should be unique but this approach is dangerous because from the time I get the value for newId, another record could be added to the table. I read that locking the table would fix this but I'm not sure how I should achieve this with Entity Framework.

Also, I thought maybe doing this in a Transaction would be enough but someone said that it's not.

Update

Suppose this is the table definition in Entity Framework.

public class OnlinePaymentInfo
{
    public OnlinePaymentInfo()
    {
        Orders = new List<Order>();
    }

    [Key]
    public int Id { get; set; }

    public int InvoiceNumber { get; set; }

    public int Test { get; set; }
    //..rest of the table
}

Now, obviously Id is the primary key of the table. And I can mark InvoiceNumber as Idenity with this:

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

Now, this will work if I do something like this:

var op = new OnlinePayment { Test = 1234 };
db.OnlinePayments.Add(op);
await db.SaveChangesAsync();

And it will automatically generate a unique number for InvoiceNumber. But I need this number to change whenever I change the record. For instance:

var op = await db.OnlinePayments.FindAsync(2);
op.Test = 234243;
//Do something to invalidate/change InvoideNumber here
db.SaveChangesAsync();

I tried to set it to 0 but when I try to assign a value to it, I get an error indicating that I cannot assign to an Identity column. That's the reason I tried to do this manually.

Answer 1

One option you could do this is by creating relation to

public class OnlinePaymentInfo
{
    public OnlinePaymentInfo()
    {
        Orders = new List<Order>();
    }

    [Key]
    public int Id { get; set; }

    [Key, ForeignKey("InvoiceNumber")]
    public InvoiceNumber InvoiceNumber { get; set; }

    public int Test { get; set; }
    //..rest of the table
}

public class InvoiceNumber
{
    public InvoiceNumber()
    {
    }

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

And when you are saving the OnlinePaymentInfo, you would do

paymentInfo.InvoiceNumber = new InvoiceNumber();

And then EF would create a new identity for the InvoiceNumber and that would be unique.

ps. Not entirely sure about the syntax on code first.

Answer 2

If your mission is just to keep invoice numbers unique, you can use rowversion or uniqueidentifier types for this, but I agree, this is not readable. One approach would be having saparate table like:

Create Table InvoiceNumbers(ID bigint identity(1, 1))

Then you can create stored procedure that will insert one row to this table and return inserted identity like:

Create Procedure spGenerateInvoiceNumber
@ID bigint output
As
Bebin
    Insert into InvoiceNumbers default values
    Set @ID = scope_identity()
End

Then everywhere you need to generate new number just call that proc and get next unique value for invoice number. Notice that you can get gaps with this approach.

Here is an example of how to call stored proc with code first approach and get output parameter EF4.1 Code First: Stored Procedure with output parameter

If you have version of Sql Server 2012+ then you can use new feature Sequence objects instead of creating new table for generating identity values.