CODEWITHSUNDEEP
c#sql-serverasp.net-mvcentity-framework
nav
nav

Reputation: 509

Entity Framework 5 : Get Auto_Increment value before insert

I currently have a table as defined as follows:

CREATE TABLE ( 
    ID int identity(1,1) primary key not null,
    field_one nvarchar(max) not null
)

I am using Entity framework 5 to insert records into this table. This aspect works and I am inserting records correctly.

field_one is a value based on the current id. For example if I have records where the max ID is 5, the next record would be inserted as (6,ABC6), and then (7, ABC7) and so on.

What I am running into is when the table is empty. The identity is at 7, and so the next record should be (8,ABC8). I Need to get the id of the auto_increment prior to the insert in order for me to apply the calculations I need when the table is empty.

How would I be able to do this since I know that the id is generated after the insert and the property is in my entity gets updated after.

Upvotes: 1

Views: 3372

Answers (1)

Mikael Östberg
Mikael Östberg

Reputation: 17146

You cannot get the correct identity before you perform SaveChanges. This is due to the fact that it is set by Entity Framework on insert.

However, there is a workaround to your problem.

Consider this:

// wrap in a transaction so that the entire operation 
// either succeeds or fails as a whole
using(var scope = new TransactionScope())
using(var context = new DbContext()) {
    var item = new Item();
    context.Items.Add(item);
    context.SaveChanges();
    // item.ID now contains the identifier
    item.field_one = string.Format("abc{0}", item.ID);
    // perform another save.
    context.SaveChanges();
    // commit transaction
    scope.Complete();
}

Yes, there are two calls to the database but there's no other way unless you are ready to go deeper than Entity Framework.

Upvotes: 5

Related Questions