Entity Framework 5 : Get Auto_Increment value before insert

Question

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.

You can't, it's because it's not created yet. But you can take `select top(id) from table` + 1, but if your design is ended up with this, you doing something wrong. — hackp0int, Nov 18 '13 at 19:38
top id is not a great way because you might have had some removed records and in that case top id will not be equal as the next generated id. you have to save changes and then the generated id will be returned. — JC Lizard, Nov 18 '13 at 19:44
http://stackoverflow.com/questions/17523568/entity-framework-retrieve-id-before-savechanges-inside-a-transaction — Sergey Vyacheslavovich Brunov, Nov 18 '13 at 19:46

Mikael Östberg · Answer 1 · 2013-11-18T20:26:04.327

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.

Entity Framework 5 : Get Auto_Increment value before insert

1 Answers1