Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

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

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(); 
like image 950
user1948635 Avatar asked Jul 08 '13 09:07

user1948635


People also ask

How do I get ID before SaveChanges?

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.

How do I get the identity column value after insert in Entity Framework?

EF execute each INSERT command followed by SELECT scope_identity() statement. SCOPE_IDENTITY returns the last identity value inserted into an identity column in the same scope. The above example will execute the following SQL in the database. WHERE @@ROWCOUNT = 1 AND [StudentID] = scope_identity();

What does the DbContext SaveChanges () method return?

Returns. The number of state entries written to the underlying database.

When would you use SaveChanges false AcceptAllChanges ()?

Sometimes though the SaveChanges(false) + AcceptAllChanges() pairing is useful. The most useful place for this is in situations where you want to do a distributed transaction across two different Contexts. If context1. SaveChanges() succeeds but context2.


2 Answers

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.

like image 137
zmbq Avatar answered Sep 16 '22 13:09

zmbq


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

like image 27
B12Toaster Avatar answered Sep 17 '22 13:09

B12Toaster