CODEWITHSUNDEEP
c#multithreadingentity-frameworktask-parallel-librarynopcommerce
Dharmik Bhandari
Dharmik Bhandari

Reputation: 1702

Getting database connection error while using parallelized foreach

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?

Upvotes: 1

Views: 2395

Answers (1)

S.N
S.N

Reputation: 5140

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.

Upvotes: 1

Related Questions