从的SQLException实体框架 - 新的交易是不允许的，因为在会话中运行其他线程 [英] SqlException from Entity Framework - New transaction is not allowed because there are other threads running in the session

问题描述

我目前正在此错误：

  

System.Data.SqlClient.SqlException：新的事务是不允许的，因为在会话中运行其他线程

。

在运行此code：

 公共类ProductManager：IProductManager
{
    #REGION声明模型
    私人RivWorks.Model.Negotiation.RIV_Entities _dbRiv = RivWorks.Model.Stores.RivEntities（AppSettings.RivWorkEntities_connString）;
    私人RivWorks.Model.NegotiationAutos.RivFeedsEntities _dbFeed = RivWorks.Model.Stores.FeedEntities（AppSettings.FeedAutosEntities_connString）;
    #endregion    公共IProduct GetProductById（GUID的productId）
    {
        //做饲料的快速同步...
        SyncFeeds（）;
        ...
        //获取产品...
        ...
        返回产品;
    }    私人无效SyncFeeds（）
    {
        布尔发现= FALSE;
        字符串feedSource =AUTO;
        开关（feedSource）// companyFeedDetail.FeedSourceTable.ToUpper（））
        {
            案AUTO：
                VAR clientList =从在_dbFeed.Client.Include（自动）选择;
                的foreach（在clientList RivWorks.Model.NegotiationAutos.Client客户端）
                {
                    从在那里_dbRiv.AutoNegotiationDetails == a.ClientID选择client.ClientID一个变种companyFeedDetailList =;
                    的foreach（RivWorks.Model.Negotiation.AutoNegotiationDetails companyFeedDetail在companyFeedDetailList）
                    {
                        如果（companyFeedDetail.FeedSourceTable.ToUpper（）==自动）
                        {
                            VAR公司=（来自于_dbRiv.Company.Include（产品），其中a.CompanyId == companyFeedDetail.CompanyId选择）。首先（）;
                            的foreach（RivWorks.Model.NegotiationAutos.Auto sourceProduct在client.Auto）
                            {
                                的foreach（RivWorks.Model.Negotiation.Product targetProduct在company.Product）
                                {
                                    如果（targetProduct.alternateProductID == sourceProduct.AutoID）
                                    {
                                        发现= TRUE;
                                        打破;
                                    }
                                }
                                如果（！找到）
                                {
                                    VAR新品推荐=新RivWorks.Model.Negotiation.Product（）;
                                    newProduct.alternateProductID = sourceProduct.AutoID;
                                    newProduct.isFromFeed = TRUE;
                                    newProduct.isDeleted = FALSE;
                                    newProduct.SKU = sourceProduct.StockNumber;
                                    company.Product.Add（新品推荐）;
                                }
                            }
                            _dbRiv.SaveChanges（）; // ###这打破### //
                        }
                    }
                }
                打破;
        }
    }
}
 

示范＃1 - 这个模型坐落在我们的开发服务器上的数据库。

示范＃2 - 这个模型坐落在我们的正式版服务器上的数据库，并通过自动饲料，每天更新。

注 - 红色型号＃1圈项目是我使用的地图到模型＃2的字段。请忽略型号＃2的红色圆圈：这是另外一个问题我也就是现在回答

。

请注意：我还需要放在一个检查请将isDeleted这样我就可以从软删除DB1它，如果它已经走了我们客户的库存

所有我想做的事情，用这种特殊的code，是一家在DB1与客户端在连接DB2，从DB2中得到他们的产品清单，并把它在DB1，如果它已不存在。第一时间通过应该是库存的全面拉动。每次后一无所获那里运行的时间应该发生，除非新的库存来在饲料过夜。

  

因此，最大的问题 - 怎样才能解决我的交易错误我得到？我是否需要删除，并通过循环每次（没有道理给我）？我重新上下文

解决方案

多拉出的头发后，我发现，的foreach 循环是罪犯。有什么需要做的就是调用EF但它返回到的IList＆LT; T＆GT; T＆GT; 的目标类型，然后在的IList＆LT循环code>。

例如：

 的IList＆LT;客户＆GT; clientList从在_dbFeed.Client.Include =（自动）选择;
的foreach（在clientList RivWorks.Model.NegotiationAutos.Client客户端）
{
   从在那里_dbRiv.AutoNegotiationDetails == a.ClientID选择client.ClientID一个变种companyFeedDetailList =;
    // ...
}
 

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

解决方案

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

这篇关于从的SQLException实体框架 - 新的交易是不允许的，因为在会话中运行其他线程的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！