CODEWITHSUNDEEP
c#.netentity-frameworkazure-sql-databaseentity-framework-6
Dave New
Dave New

Reputation: 40092

Parallelization of queries using multiple Entity Framework contexts

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:

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

Upvotes: 2

Views: 1700

Answers (1)

Gert Arnold
Gert Arnold

Reputation: 109255

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.

Upvotes: 1

Related Questions