Entity Framework - retrieve ID before 'SaveChanges' inside a transaction

Question

In Entity Framework - Is there any way to retrieve a newly created ID (identity) inside a transaction before calling 'SaveChanges'?

I need the ID for a second insert, however it is always returned as 0...

        ObjectContext objectContext = ((IObjectContextAdapter)context).ObjectContext;

        objectContext.Connection.Open();

        using (var transaction = objectContext.Connection.BeginTransaction())
        {
            foreach (tblTest entity in saveItems)
            {
                this.context.Entry(entity).State = System.Data.EntityState.Added;
                this.context.Set<tblTest>().Add(entity);

                int testId = entity.TestID;

                .... Add another item using testId
            }

            try
            {
                context.SaveChanges();
                transaction.Commit();
            }
            catch (Exception ex)
            {
                transaction.Rollback();
                objectContext.Connection.Close();
                throw ex;
            }
        }

        objectContext.Connection.Close();

Answer 1

The ID is generated by the database after the row is inserted to the table. You can't ask the database what that value is going to be before the row is inserted.

You have two ways around this - the easiest would be to call SaveChanges. Since you are inside a transaction, you can roll back in case there's a problem after you get the ID.

The second way would be not to use the database's built in IDENTITY fields, but rather implement them yourself. This can be very useful when you have a lot of bulk insert operations, but it comes with a price - it's not trivial to implement.

EDIT: SQL Server 2012 has a built-in SEQUENCE type that can be used instead of an IDENTITY column, no need to implement it yourself.

Answer 2

As others have already pointed out, you have no access to the increment value generated by the database before saveChanges() was called – however, if you are only interested in the id as a means to make a connection to another entity (e.g. in the same transaction) then you can also rely on temporary ids assigned by EF Core:

Depending on the database provider being used, values may be generated client side by EF or in the database. If the value is generated by the database, then EF may assign a temporary value when you add the entity to the context. This temporary value will then be replaced by the database generated value during SaveChanges().

Here is an example to demonstrate how this works. Say MyEntity is referenced by MyOtherEntity via property MyEntityId which needs to be assigned before saveChanges is called.

var x = new MyEntity();        // x.Id = 0
dbContext.Add(x);              // x.Id = -2147482624 <-- EF Core generated id
var y = new MyOtherEntity();   // y.Id = 0
dbContext.Add(y);              // y.Id = -2147482623 <-- EF Core generated id
y.MyEntityId = x.Id;           // y.MyEntityId = -2147482624
dbContext.SaveChangesAsync();
Debug.WriteLine(x.Id);         // 1261 <- EF Core replaced temp id with "real" id
Debug.WriteLine(y.MyEntityId); // 1261 <- reference also adjusted by EF Core

The above also works when assigning references via navigational properties, i.e. y.MyEntity = x instead of y.MyEntityId = x.Id

Answer 3

You can retreive an ID before calling .SaveChanges() by using the Hi/Lo alhorithm. The id will be assigned to the object once it is added to dbcontext.

Example configuration with fluent api:

protected override void OnModelCreating(ModelBuilder modelBuilder)
{
    modelBuilder.Entity<Entity>(e =>
    {
        e.Property(x => x.Id).UseHiLo();
    });
}

An excerpt from the relevant Microsoft article:

The Hi/Lo algorithm is useful when you need unique keys before committing changes. As a summary, the Hi-Lo algorithm assigns unique identifiers to table rows while not depending on storing the row in the database immediately. This lets you start using the identifiers right away, as happens with regular sequential database IDs.

Answer 4

If your tblTest entity is connected to other entities that you want to attach, you don't need to have the Id to create the relation. Lets say tblTest is attached to anotherTest object, it the way that in anotherTest object you have tblTest object and tblTestId properties, in that case you can have this code:

using (var transaction = objectContext.Connection.BeginTransaction())
    {
        foreach (tblTest entity in saveItems)
        {
            this.context.Entry(entity).State = System.Data.EntityState.Added;
            this.context.Set<tblTest>().Add(entity);

            anotherTest.tblTest = entity;
            ....
        }
    }

After submitting the relation would be created and you don't need to be worry about Ids and etc.

Answer 5

@zmbq is right, you can only get the id after calling save changes.

My suggestion is that you should NOT rely on the generated ID's of the database. The database should only a detail of your application, not an integral and unchangeable part.

If you can't get around that issue use a GUID as an identifier due it's uniqueness. MSSQL supports GUID as a native column type and it's fast (though not faster than INT.).

Cheers

Answer 6

A simple work around for this would be

var ParentRecord = new ParentTable () {
SomeProperty = "Some Value",
AnotherProperty = "Another Property Value"
};

ParentRecord.ChildTable.Add(new ChildTable () {
ChildTableProperty = "Some Value",
ChildTableAnotherProperty = "Some Another Value"
});

db.ParentTable.Add(ParentRecord);

db.SaveChanges();

Where ParentTable and ChildTable are two tables connected with Foregin key.

Answer 7

You can look up the value in the ChangeTracker like this:

var newEntity = new MyEntity();
var newEntity.Property = 123;
context.Add(newEntity);

//specify a logic to identity the right entity here:
var entity = context.ChangeTracker.Entries()
    .FirstOrDefault(e => e.Entity is MyEntity myEntity && 
        myEntity.Property == newEntity.Property);
//In this case we look up the value for an autogenerated id of type int/long
 //it will be a negative value like -21445363467
var value = entity.Properties?
    .FirstOrDefault(pe => pe.Metadata.GetColumnName() == nameof(MyEntity.Id))?.CurrentValue;    
//if you set it on another entity, it will be replaced on SaveChanges()

My setup was mysql 5.7, but should work in other environments also.