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

Question

I am currently getting this error:

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

while running this code:

public class ProductManager : IProductManager
{
    #region Declare Models
    private RivWorks.Model.Negotiation.RIV_Entities _dbRiv = RivWorks.Model.Stores.RivEntities(AppSettings.RivWorkEntities_connString);
    private RivWorks.Model.NegotiationAutos.RivFeedsEntities _dbFeed = RivWorks.Model.Stores.FeedEntities(AppSettings.FeedAutosEntities_connString);
    #endregion

    public IProduct GetProductById(Guid productId)
    {
        // Do a quick sync of the feeds...
        SyncFeeds();
        ...
        // get a product...
        ...
        return product;
    }

    private void SyncFeeds()
    {
        bool found = false;
        string feedSource = "AUTO";
        switch (feedSource) // companyFeedDetail.FeedSourceTable.ToUpper())
        {
            case "AUTO":
                var 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;
                    foreach (RivWorks.Model.Negotiation.AutoNegotiationDetails companyFeedDetail in companyFeedDetailList)
                    {
                        if (companyFeedDetail.FeedSourceTable.ToUpper() == "AUTO")
                        {
                            var company = (from a in _dbRiv.Company.Include("Product") where a.CompanyId == companyFeedDetail.CompanyId select a).First();
                            foreach (RivWorks.Model.NegotiationAutos.Auto sourceProduct in client.Auto)
                            {
                                foreach (RivWorks.Model.Negotiation.Product targetProduct in company.Product)
                                {
                                    if (targetProduct.alternateProductID == sourceProduct.AutoID)
                                    {
                                        found = true;
                                        break;
                                    }
                                }
                                if (!found)
                                {
                                    var newProduct = new RivWorks.Model.Negotiation.Product();
                                    newProduct.alternateProductID = sourceProduct.AutoID;
                                    newProduct.isFromFeed = true;
                                    newProduct.isDeleted = false;
                                    newProduct.SKU = sourceProduct.StockNumber;
                                    company.Product.Add(newProduct);
                                }
                            }
                            _dbRiv.SaveChanges();  // ### THIS BREAKS ### //
                        }
                    }
                }
                break;
        }
    }
}

Model #1 - This model sits in a database on our Dev Server.

Model #2 - This model sits in a database on our Prod Server and is updated each day by automatic feeds.

Note - The red circled items in Model #1 are the fields I use to "map" to Model #2. Please ignore the red circles in Model #2: that is from another question I had which is now answered.

Note: I still need to put in an isDeleted check so I can soft delete it from DB1 if it has gone out of our client's inventory.

All I want to do, with this particular code, is connect a company in DB1 with a client in DB2, get their product list from DB2 and INSERT it in DB1 if it is not already there. First time through should be a full pull of inventory. Each time it is run there after nothing should happen unless new inventory came in on the feed over night.

So the big question - how to I solve the transaction error I am getting? Do I need to drop and recreate my context each time through the loops (does not make sense to me)?

TIA

Answer 1

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;
    // ...
}

Answer 2

As you've already identified, you cannot save from within a foreach that is still drawing from the database via an active reader.

Calling ToList() or ToArray() is fine for small data sets, but when you have thousands of rows, you will be consuming a large amount of memory.

It's better to load the rows in chunks.

public static class EntityFrameworkUtil
{
    public static IEnumerable<T> EnumerateInChunksOf<T>(this IEnumerable<T> enumerable, int chunkSize)
    {
        foreach (var chunk in enumerable.GetChunksOfSize(chunkSize))
        {
            foreach (T item in chunk)
                yield return item;
        }
    }
    public static IEnumerable<T[]> GetChunksOfSize<T>(this IEnumerable<T> enumerable, int chunkSize)
    {
        int count = enumerable.Count();
        for (int chunkIndex = 0; chunkIndex * chunkSize < count; chunkIndex++)
            yield return enumerable.Skip(chunkIndex * chunkSize).Take(chunkSize).ToArray();
    }
}

Given the above extension methods, you can write your query like this:

foreach (var client in clientList.EnumerateInChunksOf(100))
{
    // do stuff
    context.SaveChanges();
}

This version will query the database in batches of 100. Note that SaveChanges() is called for each entity.

If you want to improve your throughput dramatically, you should call SaveChanges() less frequently. Use code like this instead:

foreach (var chunk in clientList.GetChunksOfSize(100))
{
    foreach (var client in chunk)
    {
        // do stuff
    }
    context.SaveChanges();
}

This results in 100 times fewer database update calls. Of course each of those calls takes longer to complete, but you still come out way ahead in the end. Your mileage may vary, but this was worlds faster for me.

And it gets around the exception you were seeing.