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

asp.net, entity-framework, transactions

Solution

This might be because you are enumerating results when trying to save changes.

Try changing this line:

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

to:

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

As a side note invoking `.SaveChanges()` inside a loop is usually not a good idea as it is in general an expensive operation (talks to the database).

Problem

I am getting the following error trying to save changes in entity framework - System.Data.SqlClient.SqlException: New transaction is not allowed because there are other threads running in the session. I have seen various answers to this problem, but I cannot seem to get any of them to work, basically I am saving a large number of items in a transaction inside my repository, I have to loop through several items to delete them and write an audit record. All other answers I have seen for this (E.g. Mark Staffords Answer) suggest declaring an explicit transaction (which I have) or by only calling save changes after completing the loop (this is not an option due to the way auditing currently works - the audit ID is required to write the audit details records). The error is thrown 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"); } } ```

Original source