来自Entity Framework的SqlException - 不允许新的事务，因为会话中还有其他线程运行 [英] SqlException from Entity Framework - New transaction is not allowed because there are other threads running in the session

问题描述

我正在收到此错误：

System.Data.SqlClient.SqlException：新事务不允许，因为有其他线程运行此代码时

  public class ProductManager：IProductManager 
 {
 #region声明模型
 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）
 {
 //快速同步Feed ... 
 SyncFeeds（）; 
 ... 
 //获取产品... 
 ... 
返回产品; 
} 
 
 private void SyncFeeds（）
 {
 bool found = false; 
 string feedSource =AUTO; 
 switch（feedSource）// companyFeedDetail.FeedSourceTable.ToUpper（））
 {
 caseAUTO：
 var clientList =从_dbFeed.Client.Include（Auto ） 选择一个; 
 foreach（在CustomerList中的RivWorks.Model.NegotiationAutos.Client客户端）
 {
 var companyFeedDetailList =从_dbRiv.AutoNegotiationDetails中的a.ClientID == client.ClientID选择一个; 
 foreach（RivWorks.Model.Negotiation.AutoNegotiationDetails companyFeedDetail in companyFeedDetailList）
 {
 if（companyFeedDetail.FeedSourceTable.ToUpper（）==AUTO）
 {
 var company =（from a in _dbRiv.Company.Include（Product）其中a.CompanyId == companyFeedDetail.CompanyId选择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; 
} 
} 
} 
  

模型＃1 - 此模型坐在我们的Dev Server的数据库中。
Model＃1 http ：//content.screencast.com/users/Keith.Barrows/folders/Jing/media/bdb2b000-6e60-4af0-a7a1-2bb6b05d8bc1/Model1.png

模型＃2 - 此模型位于我们的Prod服务器上的数据库中，并且每天通过自动提要更新。 alt text http：// content。 screencast.com/users/Keith.Barrows/folders/Jing/media/4260259f-bce6-43d5-9d2a-017bd9a980d4/Model2.png

注意 - 型号1中的红色圆圈项目是我用于映射到型号2的字段。请忽略模型＃2中的红色圆圈：这是从现在回答的另一个问题。

注意：我仍然需要放入一个isDeleted检查，所以我可以从DB1软件中删除它，如果它已经脱离了我们客户的库存。

我想用这个特定的代码，将DB1中的一个公司连接到DB2中的客户端，从DB2获取其产品列表，并将其插入DB1（如果尚未存在）。首次通过应该是全面的库存。每当它运行在那里，没有任何事情发生，除非新的库存进来的饲料过夜。

所以大问题 - 我如何解决交易错误我得到？每次通过循环，我需要删除并重建我的上下文（对我来说没有意义）？

解决方案

经过多次拔出头发，我发现 foreach 循环是罪魁祸首。需要做的是呼叫EF，但将其返回到目标类型的 IList 中，然后循环在 IList< T> code>。

示例：

  IList< Client> ; clientList =从_dbFeed.Client.Include（Auto）中选择一个; 
 foreach（在CustomerList中的RivWorks.Model.NegotiationAutos.Client客户端）
 {
 var companyFeedDetailList =从_dbRiv.AutoNegotiationDetails中的a.ClientID == client.ClientID选择一个; 
 // ... 
} 
  

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

解决方案

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

这篇关于来自Entity Framework的SqlException - 不允许新的事务，因为会话中还有其他线程运行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！