Geek Answers Handbook

Table lock in 2 statements in Entity Framework

I have the following statements:

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

The column InvoiceNumbermust be unique, but this approach is dangerous, since from the moment you get the value for newId, you can add another record to the table. I read that table locking will fix this, but I'm not sure how I should achieve this using the Entity Framework.

In addition, I thought that perhaps doing this in a transaction would be enough, but someone said that it is not.

Update

Suppose this is a table definition in the 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, Idis the primary key of the table. And I can note InvoiceNumberhow Idenity is 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();

InvoiceNumber. , , . :

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

0, , , , Identity. , .

+4
6

, , -

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

OnlinePaymentInfo,

paymentInfo.InvoiceNumber = new InvoiceNumber();

EF InvoiceNumber .

. .

+2

, , rowversion uniqueidentifier, , . saparate, :

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

, , :

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

, proc -. , .

EF4.1. :

Sql Server 2012+, Sequence objects .

+3

InvoiceNumber. , , .

SQL Server, rowversion (timestamp).

rowversion/timestamp. rowversion .

rowversion , SQL Server.

, SQL Server :

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

, SQL, .

. SQL Fiddle.

+2

( )

.

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;

, , , db . , .

, , - int myNewId = tableMaxId + 1;, , - , .

, , . , , - .

. , , , , .

- :

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

. , , , , . , .

+1

. , EF. SQL rowversion

    EF

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

.

. . SQL Server

-

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

Id .

+1

:

:

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

( -)

insert into NamedSequence values ('INVOICE_NO', 1)

proc:

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

, , - . -, , .

+1

More articles:


All Articles