实体框架-在事务中“保存更改”之前检索 ID

在实体框架中——在调用“ SaveChanges”之前，是否有办法在事务中检索新创建的 ID (身份) ？

我需要第二次插入的 ID，但它总是返回为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();
96089 次浏览
小开
最佳答案

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.

小开

@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

小开

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.

小开

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

小开

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.

小开

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.

小开

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.


提交答案