System.InvalidOperationException: ExecuteReader requires an open and available Connection. The connection's current state is open

Question

Hi So I have an error appearing in logs, and some functionality is sometimes broken, it's intermittent though.

The full stack trace looks lie this:

System.Data.Entity.Core.EntityCommandExecutionException: An error occurred while executing the command definition. See the inner exception for details. ---> System.InvalidOperationException: ExecuteReader requires an open and available Connection. The connection's current state is open. at System.Data.SqlClient.SqlConnection.GetOpenConnection(String method) at System.Data.SqlClient.SqlConnection.ValidateConnectionForExecute(String method, SqlCommand command) at System.Data.SqlClient.SqlCommand.ValidateCommand(String method, Boolean async) at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, TaskCompletionSource1 completion, Int32 timeout, Task& task, Boolean& usedCache, Boolean asyncWrite, Boolean inRetry) at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method) at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior, String method) at System.Data.Entity.Infrastructure.Interception.InternalDispatcher1.Dispatch[TTarget,TInterceptionContext,TResult](TTarget target, Func3 operation, TInterceptionContext interceptionContext, Action3 executing, Action3 executed) at System.Data.Entity.Infrastructure.Interception.DbCommandDispatcher.Reader(DbCommand command, DbCommandInterceptionContext interceptionContext) at System.Data.Entity.Core.EntityClient.Internal.EntityCommandDefinition.ExecuteStoreCommands(EntityCommand entityCommand, CommandBehavior behavior) --- End of inner exception stack trace --- at System.Data.Entity.Core.EntityClient.Internal.EntityCommandDefinition.ExecuteStoreCommands(EntityCommand entityCommand, CommandBehavior behavior) at System.Data.Entity.Core.Objects.Internal.ObjectQueryExecutionPlan.Execute[TResultType](ObjectContext context, ObjectParameterCollection parameterValues) at System.Data.Entity.Core.Objects.ObjectContext.ExecuteInTransaction[T](Func1 func, IDbExecutionStrategy executionStrategy, Boolean startLocalTransaction, Boolean releaseConnectionOnSuccess) at System.Data.Entity.Core.Objects.ObjectQuery1.<>c__DisplayClass7.<GetResults>b__5() at System.Data.Entity.SqlServer.DefaultSqlExecutionStrategy.Execute[TResult](Func1 operation) at System.Data.Entity.Core.Objects.ObjectQuery1.GetResults(Nullable1 forMergeOption) at System.Data.Entity.Core.Objects.DataClasses.EntityCollection1.Load(List1 collection, MergeOption mergeOption) at System.Data.Entity.Core.Objects.DataClasses.RelatedEnd.DeferredLoad() at System.Data.Entity.Core.Objects.Internal.LazyLoadBehavior.LoadProperty[TItem](TItem propertyValue, String relationshipName, String targetRoleName, Boolean mustBeNull, Object wrapperObject) at System.Data.Entity.Core.Objects.Internal.LazyLoadBehavior.<>c__DisplayClass72.<GetInterceptorDelegate>b__1(TProxy proxy, TItem item) at System.Data.Entity.DynamicProxies.Product_49EFA7CB993633FA6F92A211A42F2C206E79CC0974B0D20D2E9E66248C8DC082.get_ProductSpecificationAttributes() at Nop.Plugin.Widgets.Enhancements.Controllers.ProductController.ProductDetail_SizesDropDownStockLevelsOverall(Int32 productId, String productColor) at lambda_method(Closure , ControllerBase , Object[] ) at System.Web.Mvc.ReflectedActionDescriptor.Execute(ControllerContext controllerContext, IDictionary2 parameters) at System.Web.Mvc.ControllerActionInvoker.InvokeActionMethod(ControllerContext controllerContext, ActionDescriptor actionDescriptor, IDictionary2 parameters) at System.Web.Mvc.Async.AsyncControllerActionInvoker.<BeginInvokeSynchronousActionMethod>b__39(IAsyncResult asyncResult, ActionInvocation innerInvokeState) at System.Web.Mvc.Async.AsyncResultWrapper.WrappedAsyncResult2.CallEndDelegate(IAsyncResult asyncResult) at System.Web.Mvc.Async.AsyncControllerActionInvoker.EndInvokeActionMethod(IAsyncResult asyncResult) at System.Web.Mvc.Async.AsyncControllerActionInvoker.AsyncInvocationWithFilters.b__3d() at System.Web.Mvc.Async.AsyncControllerActionInvoker.AsyncInvocationWithFilters.<>c__DisplayClass46.b__3f() at System.Web.Mvc.Async.AsyncControllerActionInvoker.AsyncInvocationWithFilters.<>c__DisplayClass46.b__3f() at System.Web.Mvc.Async.AsyncControllerActionInvoker.AsyncInvocationWithFilters.<>c__DisplayClass46.b__3f() at System.Web.Mvc.Async.AsyncControllerActionInvoker.AsyncInvocationWithFilters.<>c__DisplayClass46.b__3f() at System.Web.Mvc.Async.AsyncControllerActionInvoker.AsyncInvocationWithFilters.<>c__DisplayClass46.b__3f() at System.Web.Mvc.Async.AsyncControllerActionInvoker.AsyncInvocationWithFilters.<>c__DisplayClass46.b__3f() at System.Web.Mvc.Async.AsyncControllerActionInvoker.AsyncInvocationWithFilters.<>c__DisplayClass46.b__3f() at System.Web.Mvc.Async.AsyncControllerActionInvoker.AsyncInvocationWithFilters.<>c__DisplayClass46.b__3f() at System.Web.Mvc.Async.AsyncControllerActionInvoker.EndInvokeActionMethodWithFilters(IAsyncResult asyncResult) at System.Web.Mvc.Async.AsyncControllerActionInvoker.<>c__DisplayClass21.<>c__DisplayClass2b.b__1c() at System.Web.Mvc.Async.AsyncControllerActionInvoker.<>c__DisplayClass21.b__1e(IAsyncResult asyncResult) at System.Web.Mvc.Async.AsyncControllerActionInvoker.EndInvokeAction(IAsyncResult asyncResult) at System.Web.Mvc.Controller.b__1d(IAsyncResult asyncResult, ExecuteCoreState innerState) at System.Web.Mvc.Async.AsyncResultWrapper.WrappedAsyncVoid1.CallEndDelegate(IAsyncResult asyncResult) at System.Web.Mvc.Controller.EndExecuteCore(IAsyncResult asyncResult) at System.Web.Mvc.Async.AsyncResultWrapper.WrappedAsyncVoid1.CallEndDelegate(IAsyncResult asyncResult) at System.Web.Mvc.Controller.EndExecute(IAsyncResult asyncResult) at System.Web.Mvc.MvcHandler.b__5(IAsyncResult asyncResult, ProcessRequestState innerState) at System.Web.Mvc.Async.AsyncResultWrapper.WrappedAsyncVoid`1.CallEndDelegate(IAsyncResult asyncResult) at System.Web.Mvc.MvcHandler.EndProcessRequest(IAsyncResult asyncResult) at System.Web.HttpApplication.CallHandlerExecutionStep.System.Web.HttpApplication.IExecutionStep.Execute() at System.Web.HttpApplication.ExecuteStepImpl(IExecutionStep step) at System.Web.HttpApplication.ExecuteStep(IExecutionStep step, Boolean& completedSynchronously)

I seen some solutions about multiple active result sets in the connection string, but apparently thats a workaround and not a solutiuon.

The stack trace takes me to this section of code, though I can't quite work out what it is.

[HttpPost]
    public ActionResult ProductDetail_SizesDropDown(int productId, string productColor)
    {
        string dropdownOptions = "";
        string dropdownOptionsSizes = "";
        var allSizes = new List<Tuple<string, int>>();
        var product = _productService.GetProductById(productId);
        if (product == null)
        {
            return Json(new
            {
                success = false
            });
        }
        var parentProductId = (product.ParentGroupedProductId > 0) ? product.ParentGroupedProductId : productId;

        var childProducts = _cacheManager.Get("CHILD_PRODUCTS_" + parentProductId, () => _productService.GetAssociatedProducts(parentProductId));

        if (childProducts.Count() == 0)
        {
            return Json(new
            {
                success = false
            });
        }

        foreach (var childProduct in childProducts)
        {
            var sizeSpec =
                childProduct.ProductSpecificationAttributes.FirstOrDefault(
                    x => x.SpecificationAttributeOption != null && x.SpecificationAttributeOption.SpecificationAttribute != null && x.SpecificationAttributeOption.SpecificationAttribute.Name == "Size");
            if (sizeSpec != null)
            {
                var size = this.GetSpecValue(sizeSpec);
                allSizes.Add(new Tuple<string, int>(size, sizeSpec.SpecificationAttributeOption.DisplayOrder));
            }
        }

        allSizes = allSizes.Distinct().OrderBy(x => x.Item2).ToList();

        var countSizes = 0;
        foreach (var sizeTuple in allSizes)
        {
            var size = sizeTuple.Item1;
            var disabled = "";
            var selected = "";

            var sizeProduct = _cacheManager.Get("SIZE_PRODUCT_" + parentProductId + "_" + sizeTuple.Item1 + "_" + productColor, 60, () =>
            {
                if (!String.IsNullOrEmpty(productColor))
                {
                    return _productService.GetAssociatedProducts(parentProductId).FirstOrDefault(x =>
                        x.ProductSpecificationAttributes.Any(psa =>
                            psa.SpecificationAttributeOption != null && psa.SpecificationAttributeOption.SpecificationAttribute != null && psa.SpecificationAttributeOption.Name == sizeTuple.Item1 &&
                            psa.SpecificationAttributeOption.SpecificationAttribute.Name == "Size") &&
                        x.ProductSpecificationAttributes.Any(psa =>
                            psa.SpecificationAttributeOption != null && psa.SpecificationAttributeOption.SpecificationAttribute != null && psa.SpecificationAttributeOption.Name == productColor &&
                            psa.SpecificationAttributeOption.SpecificationAttribute.Name == "Colour"));
                }
                else
                {
                    return _productService.GetAssociatedProducts(parentProductId).FirstOrDefault(x =>
                        x.ProductSpecificationAttributes.Any(psa =>
                            psa.SpecificationAttributeOption != null && psa.SpecificationAttributeOption.SpecificationAttribute != null && psa.SpecificationAttributeOption.Name == sizeTuple.Item1 &&
                            psa.SpecificationAttributeOption.SpecificationAttribute.Name == "Size"));
                }
            });

            // No size child exists for this product so we don't add to the dropdown
            if (sizeProduct == null)
                continue;

            var sizeLabel = size;
            if (sizeProduct != null)
            {
                var stockLabel = RefreshProductStockLabel(sizeProduct.Id, false);

                if (stockLabel.ToLower().Contains("out of stock"))
                {
                    stockLabel = "Out of stock";
                }

                if (stockLabel.ToLower() == "out of stock" || stockLabel.ToLower() == "low stock")
                {
                    sizeLabel += " <strong class=\"" + stockLabel.ToLower().Replace(" ", "-") + "\">" + stockLabel + "</strong>";
                }

                if (stockLabel.ToLower() == "out of stock" && !sizeProduct.PreorderAvailableNew)
                {
                    disabled = " disabled='disabled'";
                    selected = "";
                }
            }

            dropdownOptionsSizes += String.Format("<option value='{0}'{2}{3} data-text='{1}'>{0}</option>",
                HttpUtility.HtmlEncode(size.Replace("\"", "")), HttpUtility.HtmlEncode(sizeLabel), disabled, selected);

            countSizes++;
        }
        dropdownOptions = "<option value=''>Choose from " + countSizes + " sizes</option>" + dropdownOptionsSizes;


        return Json(new
        {
            success = true,
            dropdownOptions
        });
    }

I think I may need to use to list, when I grab the child products though I'm not sure.

var childProducts = _cacheManager.Get("CHILD_PRODUCTS_" + parentProductId, () => _productService.GetAssociatedProducts(parentProductId));

Any guidance would be appreciated. Thanks

David Browne - Microsoft David Browne - Microsoft · Accepted Answer · 2020-04-28T23:37:40

Here's the key bit:

    System.Data.Entity.Core.Objects.DataClasses.RelatedEnd.DeferredLoad() 
at  System.Data.Entity.Core.Objects.Internal.LazyLoadBehavior.LoadProperty[TItem]

Your service is returning Entities with Lazy Loading enabled. Lazy Loading entities have a reference to the DbContext that loaded them, and so can't be accessed by multiple threads, or accessed after the DbContext instance is Disposed. So if you're returning the objects from a controller method for serialization, or caching them for use by other requests, Lazy Loading must be disabled.

You need to turn off lazy loading for entities that will get serialized or cached.

System.InvalidOperationException: ExecuteReader requires an open and available Connection. The connection's current state is open

1 Answers