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

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.

Answer 2

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

This functionality is in SQL Server provided by a rowversion (timestamp) column.

Just change the type of your table column to rowversion / timestamp. The rowversion column certainly cannot be used as a primary key.

With rowversion column you do not need to worry about table locking or transactions, its unique value is automatically maintained by SQL Server.

If you have a reason not to use timestamps, you can make use of SQL Server sequences combined with triggers:

CREATE SEQUENCE dbo.InvoiceNumberSeq
AS int
START WITH 1
INCREMENT BY 1 ;

GO

CREATE TRIGGER dbo.SetInvoiceNumber
ON dbo.OnlinePayments
AFTER INSERT, UPDATE 
AS
BEGIN
        UPDATE t
            SET t.InvoiceNumber = NEXT VALUE FOR dbo.InvoiceNumberSeq
            FROM dbo.OnlinePayments AS t 
            INNER JOIN inserted AS i ON t.ID = i.ID;
END
GO

If possible it is better to handle identity or timestamp inserts/updates within SQL database using built-in features as much as possible.

See SQL Fiddle.

Answer 3

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 4

(This answer is in relation to the comment thread above)

When adding an object to a table, it will get assigned an identity.

Person myPerson = new Person() { Name = "Flater" };

//at this point, myPerson.Id is 0 because it was not yet assigned a different value

db.People.Add(myPerson);
db.SaveChanges();

//at this point, myPerson.Id is assigned a value because it was saved!
int assignedIdentity = myPerson.Id;

Note that you do not know what the ID is until the db returns it to you. The database decided what the ID is at the time of creation.

If you preemptively check what the currently highest ID is in the table, and then do something like int myNewId = tableMaxId + 1; you will run into problem if someone else was already saving objects in the table while your code is still doing its preemptive guesses.

Never, ever guess what the ID will be. Always save the object to the database, so the database can make sure the assigned ID will never be assigned to someone else.

Note: If at this point in time, you need the ID but it might still be possible that you need to cancel the transation, have a fallback scenario where you delete the proviously created object.

Something like this:

 int the_id_that_was_assigned = 0; //currently not known yet!

try
{
        Person myPerson = new Person() { Name = "Flater" };

        db.People.Add(myPerson);
        db.SaveChanges();

        the_id_that_was_assigned = myPerson.Id; //now it is known because the object was saved.

        //do whatever processing you need to do based on that ID
        //If you want to cancel, you can throw an exception or delete the object manually
        //If you do not want to cancel, then the Person you just created is excatly what you want.
}
catch(Exception ex)
{
        if(the_id_that_was_assigned > 0)
        {
            db.People.Delete(the_id_that_was_assigned);
        }
}

This is just a quick and dirty example. I hope you get the gist of it, you need to actually create the object in order to know which identity it was assigned. Guessing the ID will lead to possible failure in cases where different applications or threads are saving object in the same database table.

Answer 5

I would recommend you dont try and reinvent the wheel. The DB has good features that EF exposes. SQL rowversion

Properties EF Code annotation timestamp

 [Timestamp]  
 public virtual byte[] RowVersion { get; set; }

you can then use an optimistic locking approach.

If you keen to have you own locking control. Use a DB feature and call it. SQL server application level locks

Another option is just use a second table

  Public class IdPool{
    int Id {get;set}
    string bla {get; set;}
  }

And just get the next row from table This provides the Id only once and is easily built.

Answer 6

You could solve this with a stored proc:

First create a table of named sequences:

CREATE TABLE [dbo].[NamedSequence](
    [SequenceName] [nchar](10) NOT NULL,
    [NextValue] [int] NOT NULL,
 CONSTRAINT [PK_NamedSequence] PRIMARY KEY CLUSTERED 
(
    [SequenceName] ASC
)

Then seed the data (you do this one time ever)

insert into NamedSequence values ('INVOICE_NO', 1)

Then this proc:

create procedure GetNextValue 
    @SequenceName varchar(10),
    @SequenceValue int out
as
update NamedSequence 
    set @SequenceValue = NextValue, NextValue = NextValue + 1 
    where SequenceName = @SequenceName
go

Because you are setting the return value in the same statement as in the update, it will be atomic -- you will never get duplicates. If you have other cases outside of Invoice number where you need sequences, you just add a row with a new key and start value to the table.