Parallel 不适用于实体框架 [英] Parallel doesnt work with Entity Framework

问题描述

我有一个 ID 列表，我需要对每个 ID 运行多个存储过程.

I have a list of IDs, and I need to run several stored procedures on each ID.

当我使用标准的 foreach 循环时，它工作正常，但是当我有很多记录时，它的工作速度很慢.

When I am using a standard foreach loop, it works OK, but when I have many records, it works pretty slow.

我想将代码转换为使用 EF，但出现异常:底层提供程序在打开时失败".

I wanted to convert the code to work with EF, but I am getting an exception: "The underlying provider failed on Open".

我在 Parallel.ForEach 中使用此代码:

I am using this code, inside the Parallel.ForEach:

using (XmlEntities osContext = new XmlEntities())
{
    //The code
}

但它仍然抛出异常.

知道如何将 Parallel 与 EF 结合使用吗?我需要为我正在运行的每个程序创建一个新的上下文吗?我有大约 10 个过程，所以我认为创建 10 个上下文非常糟糕，每个上下文一个.

Any idea how can I use Parallel with EF? do I need to create a new context for every procedure I am running? I have around 10 procedures, so I think its very bad to create 10 contexts, one for each.

推荐答案

实体框架使用的底层数据库连接是不是线程安全的.您将需要为要在另一个线程上执行的每个操作创建一个新上下文.

The underlying database connections that the Entity Framework are using are not thread-safe. You will need to create a new context for each operation on another thread that you're going to perform.

您对如何并行化操作的关注是有效的；许多上下文的打开和关闭代价高昂.

Your concern about how to parallelize the operation is a valid one; that many contexts are going to be expensive to open and close.

相反，您可能想要颠倒您对并行化代码的想法.您似乎正在循环访问多个项目，然后为每个项目依次调用存储过程.

Instead, you might want to invert how your thinking about parallelizing the code. It seems you're looping over a number of items and then calling the stored procedures in serial for each item.

如果可以，请创建一个新的Task(或Task，如果你不需要结果)为每个 procedure 然后在那个 Task 中，打开一个上下文，循环所有项目，然后执行存储过程.这样，您只有与并行运行的存储过程数量相等的上下文数量.

If you can, create a new Task<TResult> (or Task, if you don't need a result) for each procedure and then in that Task<TResult>, open a single context, loop through all of the items, and then execute the stored procedure. This way, you only have a number of contexts equal to the number of stored procedures that you are running in parallel.

假设您有一个 MyDbContext 和两个存储过程，DoSomething1 和 DoSomething2，它们都采用一个类的实例，<代码>我的项目.

Let's assume you have a MyDbContext with two stored procedures, DoSomething1 and DoSomething2, both of which take an instance of a class, MyItem.

实现上述内容类似于:

// You'd probably want to materialize this into an IList<T> to avoid
// warnings about multiple iterations of an IEnumerable<T>.
// You definitely *don't* want this to be an IQueryable<T>
// returned from a context.
IEnumerable<MyItem> items = ...;

// The first stored procedure is called here.
Task t1 = Task.Run(() => { 
    // Create the context.
    using (var ctx = new MyDbContext())
    // Cycle through each item.
    foreach (MyItem item in items)
    {
        // Call the first stored procedure.
        // You'd of course, have to do something with item here.
        ctx.DoSomething1(item);
    }
});

// The second stored procedure is called here.
Task t2 = Task.Run(() => { 
    // Create the context.
    using (var ctx = new MyDbContext())
    // Cycle through each item.
    foreach (MyItem item in items)
    {
        // Call the first stored procedure.
        // You'd of course, have to do something with item here.
        ctx.DoSomething2(item);
    }
});

// Do something when both of the tasks are done.

如果您不能并行执行存储过程(每个存储过程都依赖于以特定顺序运行)，那么您仍然可以并行化您的操作，只是稍微复杂一些.

If you can't execute the stored procedures in parallel (each one is dependent on being run in a certain order), then you can still parallelize your operations, it's just a little more complex.

您将查看创建自定义分区(使用静态 Create 方法 在 Partitioner 类).这将为您提供获取 IEnumerator 的方法 实现(注意，这是不是 IEnumerable 所以你不能foreach覆盖它).

You would look at creating custom partitions across your items (using the static Create method on the Partitioner class). This will give you the means to get IEnumerator<T> implementations (note, this is not IEnumerable<T> so you can't foreach over it).

对于返回的每个 IEnumerator 实例，您将创建一个新的 Task(如果需要结果)，并在 Taskcode>Task 主体，您将创建上下文，然后循环遍历 IEnumerator 返回的项目，按顺序调用存储过程.

For each IEnumerator<T> instance you get back, you'd create a new Task<TResult> (if you need a result), and in the Task<TResult> body, you would create the context and then cycle through the items returned by the IEnumerator<T>, calling the stored procedures in order.

看起来像这样:

// Get the partitioner.
OrdinalPartitioner<MyItem> partitioner = Partitioner.Create(items);

// Get the partitions.
// You'll have to set the parameter for the number of partitions here.
// See the link for creating custom partitions for more
// creation strategies.
IList<IEnumerator<MyItem>> paritions = partitioner.GetPartitions(
    Environment.ProcessorCount);

// Create a task for each partition.
Task[] tasks = partitions.Select(p => Task.Run(() => { 
        // Create the context.
        using (var ctx = new MyDbContext())
        // Remember, the IEnumerator<T> implementation
        // might implement IDisposable.
        using (p)
        // While there are items in p.
        while (p.MoveNext())
        {
            // Get the current item.
            MyItem current = p.Current;

            // Call the stored procedures.  Process the item
            ctx.DoSomething1(current);
            ctx.DoSomething2(current);
        }
    })).
    // ToArray is needed (or something to materialize the list) to
    // avoid deferred execution.
    ToArray();

这篇关于Parallel 不适用于实体框架的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！