如何使用 Entity Framework .SqlQuery 以便它可以同时被多个客户端使用？

Question

在我的 ASP.NET MVC 应用程序中，我有一个方法，它有一个执行存储过程的循环。要循环的项目数可以是 10,000 或更多。当一个客户端尝试一次运行报告时，这一切都很好，但是当多个客户端尝试运行报告时，它会锁定所有客户端并将错误排除在外。创建的错误是：

SqlParameter 已包含在另一个 SqlParameterCollection 中。

该错误是在我尝试使用“结果”的行上生成的，然后端点对所有用户保持锁定状态，直到重新启动应用程序。我以前从未遇到过这个问题，这也是我之前第一次使用实体框架生成大型报告。我怎样才能避免这个错误？这是代码：

foreach (var item in upcList)

{

    try

    {

        if (!string.IsNullOrEmpty(item[0]))

        {

            string upc;

            if (DropCheckDigit != true)

            {

                upc = Helpers.upc.formatBRDUpc(item[0]);

            }

            else

            {

                upc = Helpers.upc.formatBRDUpcDropCheckDigit(item[0]);

            }

            var results = BRDataDb.Database.SqlQuery<ProductMovementItem>("EXEC MoC_MoCHub_GetItemData @p0, @p1", upc, DataSource).FirstOrDefault();

            if (results != null)

            {

                if (results.BaseQty != 0 && results.BasePrice != 0)

                {

                    results.Margin = (((results.BasePrice / results.BaseQty) - results.cost) / (results.BasePrice / results.BaseQty)) * 100;

                    if (results.Margin != null)

                    {

                        results.Margin = decimal.Round(decimal.Parse(results.Margin.ToString()), 2);

                    }

                }

                PMIList.Add(results);

                progressCounter += 1;

                Helpers.SharedFunctions.SendProgress("Processing...", progressCounter, (upcList.Count * 2), connectionId);

            }

        }

    }

    catch (Exception ex)

    {

        Elmah.ErrorSignal.FromCurrentContext().Raise(ex);

    }

}

Answer 1

你试过处置吗foreach (var item in upcList)&nbsp; &nbsp; {&nbsp; &nbsp; &nbsp; &nbsp; try&nbsp; &nbsp; &nbsp; &nbsp; {&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; if (!string.IsNullOrEmpty(item[0]))&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; {&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; string upc;&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; if (DropCheckDigit != true)&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; {&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; upc = Helpers.upc.formatBRDUpc(item[0]);&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; }&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; else&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; {&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; upc = Helpers.upc.formatBRDUpcDropCheckDigit(item[0]);&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; }&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; using (BRDataDb db = new BRDataDb())&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; {&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; var results = db.Database.SqlQuery<ProductMovementItem>("EXEC MoC_MoCHub_GetItemData @p0, @p1", upc, DataSource).FirstOrDefault();&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; if (results != null)&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; {&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; if (results.BaseQty != 0 && results.BasePrice != 0)&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; {&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; results.Margin = (((results.BasePrice / results.BaseQty) - results.cost) / (results.BasePrice / results.BaseQty)) * 100;&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; if (results.Margin != null)&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; {&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; results.Margin = decimal.Round(decimal.Parse(results.Margin.ToString()), 2);&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; }&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; }&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; PMIList.Add(results);&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; progressCounter += 1;&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; Helpers.SharedFunctions.SendProgress("Processing...", progressCounter, (upcList.Count * 2), connectionId);&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; }&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; }&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; }&nbsp; &nbsp; &nbsp; &nbsp; }&nbsp; &nbsp; &nbsp; &nbsp; catch (Exception ex)&nbsp; &nbsp; &nbsp; &nbsp; {&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; Elmah.ErrorSignal.FromCurrentContext().Raise(ex);&nbsp; &nbsp; &nbsp; &nbsp; }&nbsp; &nbsp; }