Getting database connection error while using parallelized foreach

Question

I'm using nopCommerce. In that I've used GetProudctsByIds to get number of products. From that, I need to map certain entities with my CustomClass. I feel that entity mapping process is pretty much slow while number of products are greater than from certain number so that I was thinking to accelerate this process by running parallel and I decided to use Task Parallel Library .

For example,

var products = _productService.GetProductsByIds(productIds);
//sequential foreach
//This works fine.
foreach(var product in products)
{

       var obj = new CustomProduct();

                obj.Id = product .Id;
                obj.Name = product .Name;
                obj.ShortDescription = product .ShortDescription;
                obj.FullDescription = product .FullDescription;

                 //Get Product URL
          var picture = _pictureService.GetPicturesByProductId(product .Id, 1).FirstOrDefault();
          if (picture != null)
          {
                 obj.ImageUrl = _pictureService.GetPictureUrl(picture, _mediaSetting.ProductThumbPictureSize, true);
          }
    //there are many more entities are mapping here…

    objList.Add(obj);
}



//parallelized foreach
Parallel.ForEach(products, (product, state) =>
{

       var obj = new CustomProduct();

                obj.Id = product .Id;
                obj.Name = product .Name;
                obj.ShortDescription = product .ShortDescription;
                obj.FullDescription = product .FullDescription;

                 //Get Product URL
          var picture = _pictureService.GetPicturesByProductId(product .Id, 1).FirstOrDefault();
          if (picture != null)
          {
                 obj.ImageUrl = _pictureService.GetPictureUrl(picture, _mediaSetting.ProductThumbPictureSize, true);
          }
    //there are many more entities are mapping here...

    objList.Add(obj);
}

This works fine for sequential foreach loop but getting errors while using parallelized foreach loop.

Here is the Stack Trace.

System.AggregateException: One or more errors occurred. ---> System.Data.EntityException: The underlying provider failed on Open. ---> System.InvalidOperationException: The connection was not closed. The connection's current state is connecting. at System.Data.ProviderBase.DbConnectionClosedConnecting.TryOpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory, TaskCompletionSource`1 retry, DbConnectionOptions userOptions) at System.Data.SqlClient.SqlConnection.TryOpen(TaskCompletionSource`1 retry) at System.Data.SqlClient.SqlConnection.Open() at System.Data.EntityClient.EntityConnection.OpenStoreConnectionIf(Boolean openCondition, DbConnection storeConnectionToOpen, 

DbConnection originalConnection, String exceptionCode, String attemptedOperation, Boolean& closeStoreConnectionOnFailure) --- End of inner exception stack trace --- at System.Data.EntityClient.EntityConnection.OpenStoreConnectionIf(Boolean openCondition, DbConnection storeConnectionToOpen, DbConnection originalConnection, String exceptionCode, String attemptedOperation, Boolean& closeStoreConnectionOnFailure) at System.Data.EntityClient.EntityConnection.Open() at System.Data.Objects.ObjectContext.EnsureConnection() at System.Data.Objects.ObjectQuery`1.GetResults(Nullable`1 forMergeOption) at System.Data.Objects.ObjectQuery`1.System.Collections.Generic.IEnumerable<T>.GetEnumerator() at System.Data.Entity.Internal.Linq.InternalQuery`1.GetEnumerator() at System.Data.Entity.Infrastructure.DbQuery`1.System.Collections.Generic.IEnumerable<TResult>.GetEnumerator() at System.Collections.Generic.List`1..ctor(IEnumerable`1 collection) at System.Linq.Enumerable.ToList[TSource](IEnumerable`1 source) at 

Nop.Services.Media.PictureService.GetPicturesByProductId(Int32 productId, Int32 recordsToReturn) in c:\Users\Libraries\Nop.Services\Media\PictureService.cs:line 612 at Custom.Plugin.Core.CoreService.<>c__DisplayClass16.<CustomProducts>b__13(Product m, ParallelLoopState state) at System.Threading.Tasks.Parallel.<>c__DisplayClass2d`2.<ForEachWorker>b__24(Int32 i, ParallelLoopState state) at System.Threading.Tasks.Parallel.<>c__DisplayClassf`1.<ForWorker>b__c() at System.Threading.Tasks.Task.InnerInvoke() at System.Threading.Tasks.Task.InnerInvokeWithArg(Task childTask) at System.Threading.Tasks.Task.<>c__DisplayClass10.<ExecuteSelfReplicating>b__f(Object param0) --- End of inner exception stack trace --- at System.Threading.Tasks.Task.ThrowIfExceptional(Boolean includeTaskCanceledExceptions) at System.Threading.Tasks.Task.Wait(Int32 millisecondsTimeout, CancellationToken cancellationToken) at System.Threading.Tasks.Task.Wait() at System.Threading.Tasks.Parallel.ForWorker[TLocal](Int32 fromInclusive, Int32 toExclusive, ParallelOptions parallelOptions, Action`1 body, Action`2 bodyWithState, Func`4 bodyWithLocal, Func`1 localInit, Action`1 localFinally) at System.Threading.Tasks.Parallel.ForEachWorker[TSource,TLocal](IList`1 list, ParallelOptions parallelOptions, Action`1 body, Action`2 bodyWithState, Action`3 bodyWithStateAndIndex, Func`4 bodyWithStateAndLocal, Func`5 bodyWithEverything, Func`1 localInit, Action`1 localFinally) 

at System.Threading.Tasks.Parallel.ForEachWorker[TSource,TLocal](IEnumerable`1 source, ParallelOptions parallelOptions, Action`1 body, Action`2 bodyWithState, Action`3 bodyWithStateAndIndex, Func`4 bodyWithStateAndLocal, Func`5 bodyWithEverything, Func`1 localInit, Action`1 localFinally) at System.Threading.Tasks.Parallel.ForEach[TSource](IEnumerable`1 source, Action`2 body) at Custom.Plugin.Core.CoreService.CustomProducts(Int32 TotalIds, Int32 langId) at Custom.Plugin.Core.CoreService.IncrementalDataImport() ---> (Inner Exception #0) System.Data.EntityException: The underlying provider failed on Open. ---> System.InvalidOperationException: The connection was not closed. The connection's current state is connecting. at System.Data.ProviderBase.DbConnectionClosedConnecting.TryOpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory, TaskCompletionSource`1 retry, 

DbConnectionOptions userOptions) at System.Data.SqlClient.SqlConnection.TryOpen(TaskCompletionSource`1 retry) at System.Data.SqlClient.SqlConnection.Open() at System.Data.EntityClient.EntityConnection.OpenStoreConnectionIf(Boolean openCondition, DbConnection storeConnectionToOpen, DbConnection originalConnection, String exceptionCode, String attemptedOperation, Boolean& closeStoreConnectionOnFailure) --- End of inner exception stack trace --- at System.Data.EntityClient.EntityConnection.OpenStoreConnectionIf(Boolean openCondition, DbConnection storeConnectionToOpen, DbConnection originalConnection, String exceptionCode, String attemptedOperation, Boolean& closeStoreConnectionOnFailure) at System.Data.EntityClient.EntityConnection.Open() at System.Data.Objects.ObjectContext.EnsureConnection() at System.Data.Objects.ObjectQuery`1.GetResults(Nullable`1 forMergeOption) at System.Data.Objects.ObjectQuery`1.System.Collections.Generic.IEnumerable<T>.GetEnumerator() at System.Data.Entity.Internal.Linq.InternalQuery`1.GetEnumerator() at System.Data.Entity.Infrastructure.DbQuery`1.System.Collections.Generic.IEnumerable<TResult>.GetEnumerator() at System.Collections.Generic.List`1..ctor(IEnumerable`1 collection) at 

System.Linq.Enumerable.ToList[TSource](IEnumerable`1 source) at Nop.Services.Media.PictureService.GetPicturesByProductId(Int32 productId, Int32 recordsToReturn) in c:\Users\Libraries\Nop.Services\Media\PictureService.cs:line 612 at Custom.Plugin.Core.CoreService.<>c__DisplayClass16.<CustomProducts>b__13(Product m, ParallelLoopState state) at System.Threading.Tasks.Parallel.<>c__DisplayClass2d`2.<ForEachWorker>b__24(Int32 i, ParallelLoopState state) at System.Threading.Tasks.Parallel.<>c__DisplayClassf`1.<ForWorker>b__c() at System.Threading.Tasks.Task.InnerInvoke() at System.Threading.Tasks.Task.InnerInvokeWithArg(Task childTask) at System.Threading.Tasks.Task.<>c__DisplayClass10.<ExecuteSelfReplicating>b__f(Object param0)<--

What could be the reason? Why this error occurred?

S.N · Accepted Answer · 2013-11-22 10:05:27Z

1

Here you are trying to dispatch multiple thread pool thread to access resource (database) via EF. I.e your parallel loop send multiple request (dispatching multiple threads) to your underlined entity object. Unfortunately, entity connection is not thread safe and is been documented here.

answered Nov 22, 2013 at 10:05

S.N

5,1685 gold badges34 silver badges51 bronze badges

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

Collectives™ on Stack Overflow

Getting database connection error while using parallelized foreach

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