Entity Framework - "A new transaction is not allowed because there are other threads in the session"

Question

I get the following error trying to save changes to an entity structure -

System.Data.SqlClient.SqlException: A new transaction is not allowed because there are other threads in the session.

I saw various answers to this problem, but I can not get them to work, basically I save a large number of elements in a transaction inside my repository, I need to scroll through several elements to delete them and record the audit report.

All the other answers that I saw for this (e.g. Mark Staffords Answer ) offer to declare an explicit transaction (that I have) or only cause save changes after completion (this is not an option because of how the audit currently works - audit identifier is required to record audit data records).

The error is called whenever "SaveChanges" is called inside the delete method, see below -

public virtual void Save(DoseReturn oldDoseReturn) { // Get the datetime when the save started DateTime saveStartTime = DateTime.Now; Dictionary<string, object> oldValues = new Dictionary<string, object>(); Dictionary<string, object> newValues = new Dictionary<string, object>(); // Get the object context and open a new transaction ObjectContext objectContext = ((IObjectContextAdapter)context).ObjectContext; objectContext.Connection.Open(); DbTransaction transaction = objectContext.Connection.BeginTransaction(); // Use the transaction for all updates using (transaction) { if (oldDoseReturn != null) { IDoseReturnStatusRepository statusRepository = new DoseReturnStatusRepository(); var list = statusRepository.AsQueryable().Where(x => x.DoseReturnID == oldDoseReturn.DoseReturnID); foreach (var item in list) { statusRepository.Delete(item, objectRetrievedDateTime, objectContext, saveStartTime, out oldValues, out newValues); } context.SaveChanges(); // Get the relevant repository IDoseReturnsRepository repository = new DoseReturnsRepository(); // audit and delete the object repository.Delete(oldDoseReturn, objectRetrievedDateTime, objectContext, saveStartTime, out oldValues, out newValues); context.SaveChanges(); } } try { // Conduct a final save, then commit the transaction context.SaveChanges(); transaction.Commit(); } catch (Exception ex) { // An error has occurred, rollback the transaction and close the connection, then present the error transaction.Rollback(); objectContext.Connection.Close(); throw ex; } // Close the connection objectContext.Connection.Close(); } public virtual void Delete(T entity, DateTime? objectRetrievedDateTime, ObjectContext objectContext, DateTime saveStartTime, out Dictionary<string, object> oldValues, out Dictionary<string, object> newValues) { oldValues = new Dictionary<string, object>(); newValues = new Dictionary<string, object>(); if (entity == null) { throw new ArgumentException("Cannot update a null entity."); } string entityName = entity.GetType().Name; if (!objectRetrievedDateTime.HasValue || !this.AuditsAfterRetieval(objectRetrievedDateTime, entityName, entity, saveStartTime)) { this.DeletedEntityAudit(entity, out oldValues, out newValues); context.Entry(entity).State = System.Data.EntityState.Deleted; this.context.Set<T>().Remove(entity); this.Audit(entity, entityName, "Delete", oldValues, newValues, true); this.context.SaveChanges(); } else { throw new Exception("Object cannot be saved as it has been amended in another thread"); } } 

Answer 1

Perhaps this is due to the fact that you are listing the results when you try to save the changes.

Try changing this line:

 var list = statusRepository.AsQueryable() .Where(x => x.DoseReturnID == oldDoseReturn.DoseReturnID); 

in

 var list = statusRepository.AsQueryable() .Where(x => x.DoseReturnID == oldDoseReturn.DoseReturnID) .ToList(); 

As a side note, calling .SaveChanges() inside a loop is usually not a good idea, because in general it is an expensive operation (negotiating with the database).