C# – Entity Framework – retrieve ID before ‘SaveChanges’ inside a transaction

asp.netcentity-framework

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

Best Answer

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.

Related Topic