Parallelization of queries using multiple Entity Framework contexts

Question

Consider the situation where I need to perform and combine the results of several isolated queries from the database. Each individual query is based on a parameter, which is supplied from elsewhere.

One way would be to perform these queries sequentially and union the results:

public IEnumerable<SomeEntity> GetSomeEntities(List<int> parameters)
{
    var entities = new List<SomeEntity>();

    using(var context = new MyContext())
    {
        entities = parameters.SelectMany(p=> SomeComplexQuery(p);
    }

    return entities;
}

The issue with the above solution is that performance is now proportional to the size of the parameter list. SomeComplexQuery is relatively resource-intensive.

Another solution would be to use parallelization:

public IEnumerable<SomeEntity> GetSomeEntities(List<int> parameters)
{
    var entities = new List<SomeEntity>();

    Parallel.ForEach(parameters, p => 
    {
        using(var context = new MyContext())
        {
            entities.AddRange(SomeComplexQuery(p)); // Assume thread-safety
        }
    };

    return entities;
}

When I run the above solution, I get much better results, but I'm concerned:

What issues could arise from opening multiple contexts in parallel? What if we had a parameter list of size 20 or even more? Could system load result in further undesirable results?
Specifically for SQL Azure, would this be considered a bad idea due to the added latency when establishing new database connections?

I am using Entity Framework 6, Azure Web Roles and SQL Azure.

Are you trying to get lots of entities back by using parallelization? If so, perhaps a stored procedure may be more efficient. — rhughes
– rhughes, Commented May 27, 2014 at 6:28

Gert Arnold · Accepted Answer · 2014-05-27 20:40:19Z

1

It's alright to run multiple threads, each having their own context instance. I'm doing this myself in a process that may give rise to relatively many inserts or updates, so far without any problems.

The .Net connection pool is thread-safe: no physical database connection will suffer from race conditions. The .Net DbConnection objects that give access to a physical connection are not thread-safe, but each context manages its own connection objects, so they won't be shared by multiple threads. Sql Azure connections are also pooled, but you'll have to deploy retry strategies (as with any Sql Azure connection). EF6 supports connection resiliency.

Note that List<T> is not thread-safe. You better use a concurrent collection for entities.

answered May 27, 2014 at 20:40

Gert Arnold

110k36 gold badges218 silver badges319 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Parallelization of queries using multiple Entity Framework contexts

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related