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

After much pulling out of hair I discovered that the foreach loops were the culprits. What needs to happen is to call EF but return it into an IList<T> of that target type then loop on the IList<T>.

Example:

IList<Client> clientList = from a in _dbFeed.Client.Include("Auto") select a;
foreach (RivWorks.Model.NegotiationAutos.Client client in clientList)
{
   var companyFeedDetailList = from a in _dbRiv.AutoNegotiationDetails where a.ClientID == client.ClientID select a;
    // ...
}

Indeed you cannot save changes inside a foreach loop in C# using Entity Framework.

context.SaveChanges() method acts like a commit on a regular database system (RDMS).

Just make all changes (which Entity Framework will cache) and then save all of them at once calling SaveChanges() after the loop (outside of it), like a database commit command.

This works if you can save all changes at once.

Making your queryable lists to .ToList() and it should work fine.

If you get this error due to foreach and you really need to save one entity first inside loop and use generated identity further in loop, as was in my case, the easiest solution is to use another DBContext to insert entity which will return Id and use this Id in outer context

For example

    using (var context = new DatabaseContext())
    {
        ...
        using (var context1 = new DatabaseContext())
        {
            ...
               context1.SaveChanges();
        }                         
        //get id of inserted object from context1 and use is.   
      context.SaveChanges();
   }

We started seeing this error "New transaction is not allowed because there are other threads running in the session" after migrating from EF5 to EF6.

Google brought us here but we are not calling SaveChanges() inside the loop. The errors were raised when executing a stored procedure using the ObjectContext.ExecuteFunction inside a foreach loop reading from the DB.

Any call to ObjectContext.ExecuteFunction wraps the function in a transaction. Beginning a transaction while there is already an open reader causes the error.

It is possible to disable wrapping the SP in a transaction by setting the following option.

_context.Configuration.EnsureTransactionsForFunctionsAndCommands = false;

The EnsureTransactionsForFunctionsAndCommands option allows the SP to run without creating its own transaction and the error is no longer raised.

DbContextConfiguration.EnsureTransactionsForFunctionsAndCommands Property

I know it is an old question but i faced this error today.

and i found that, this error can be thrown when a database table trigger gets an error.

for your information, you can check your tables triggers too when you get this error.

Recently I faced the same issue in my project so posting my experience and it might help some on the same boat as i was. The issue was due to i am looping through the results of EF select query (results are not retrieved into memory).

var products = (from e in _context.Products
                              where e.StatusId == 1
                              select new { e.Name, e.Type });

        foreach (var product in products)
        {
           //doing some insert EF Queries
           //some EF select quries
            await _context.SaveChangesAsync(stoppingToken); // This code breaks.
        }

I have updated my Products select query to bring the results into LIST rather than IQueryable (This seems to be opening the reader throughout for each loop and hence save was failing).

 var products = (from e in _context.Products
                              where e.StatusId == 1
                              select new { e.Name, e.Type })**.ToList()**; //see highlighted

My situation was similar others above. I had an IQueryable which I was doing a foreach on. This in turn called a method with SaveChanges(). Booom exception here as there was already a transaction open from the query above.

// Example:

var myList = _context.Table.Where(x => x.time == null);

foreach(var i in myList)
{
    MyFunction(i); // <<-- Has _context.SaveChanges() which throws exception
}

Adding ToList() to the end of the query was the solution in my case.

// Fix
var myList = _context.Table.Where(x => x.time == null).ToList();