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 #1 http://content.screencast.com/users/Keith.Barrows/folders/Jing/media/bdb2b000-6e60-4af0-a7a1-2bb6b05d8bc1/Model1.png

Model #2 - This model sits in a database on our Prod Server and is updated each day by automatic feeds. alt text http://content.screencast.com/users/Keith.Barrows/folders/Jing/media/4260259f-bce6-43d5-9d2a-017bd9a980d4/Model2.png

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)?

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