C# – There is Already an open DataReader that must be closed first

asp.netasp.net-mvcasp.net-mvc-4clinq

In my mapping logic layer(Model to ViewModel) I am trying to populate a SelectListItem for use with an HTML.DropDownListFor helper in my edit view.

I attempted using a query in the following code sample to retrieve a list of brand names to populate the SelectListItem, but triggered the following exception:

There is already an open DataReader associated with this Command which
must be closed first.

Mapping

public class MedicalProductMapper
{
    private MvcMedicalStoreDb _db; // DataContext class

    public MedicalProductMapper(MvcMedicalStoreDb db)
    {
        _db = db;
    }    
    public MedicalProductViewModel GetMedicalProductViewModel(MedicalProduct source)
    {
        MedicalProductViewModel viewModel = new MedicalProductViewModel();

        viewModel.ID = source.ID; 
        viewModel.Name = source.Name;
        viewModel.Price = source.Price;
        viewModel.BrandID = source.BrandID;

        // This following line produces the exception
        viewModel.BrandName = _db.Brands.Single(b => b.ID == source.BrandID).Name;

        var queryBrands = from b in _db.Brands
                          select b;

        viewModel.BrandSelectListItem = queryBrands as IEnumerable<SelectListItem>;

        return viewModel;
    }
}

I understand that there is an easy fix, by enabling Multiple Active Result Sets (MARS) in the connection string, but I'd like to know if there's a way to do what I want without modifying the connection string.

Here are some more classes in case they are helpful in figuring out this problem:

Edit view

@model MvcMedicalStore.Models.MedicalProductViewModel

@{
    ViewBag.Title = "Edit";
}

<h2>Edit</h2>

@using (Html.BeginForm()) {
    @Html.AntiForgeryToken()
    @Html.ValidationSummary(true)

    <fieldset>
        <legend>MedicalProduct</legend>

        @Html.HiddenFor(model => model.ID)

        <div class="editor-label">
            @Html.LabelFor(model => model.Name)
        </div>
        <div class="editor-field">
            @Html.EditorFor(model => model.Name)
            @Html.ValidationMessageFor(model => model.Name)
        </div>

        <div class="editor-label">
            @Html.LabelFor(model => model.Price)
        </div>
        <div class="editor-field">
            @Html.EditorFor(model => model.Price)
            @Html.ValidationMessageFor(model => model.Price)
        </div>

        // BRAND NAME
        <div class="editor-label">
            @Html.LabelFor(model => model.BrandName)
        </div>
        <div class="editor-field">
            @Html.DropDownListFor(model => model.BrandName, Model.BrandSelectListItem)
            @Html.ValidationMessageFor(model => model.BrandName)
        </div>

        <p>
            <input type="submit" value="Save" />
        </p>
    </fieldset>
}

<div>
    @Html.ActionLink("Back to List", "Index")
</div>

@section Scripts {
    @Scripts.Render("~/bundles/jqueryval")
}

Controller:

public class MedicalProductController : Controller
{
    private MvcMedicalStoreDb _db = new MvcMedicalStoreDb();

    //
    // GET: /MedicalSupply/

    public ActionResult Index()
    {
        var viewModel = _db.Products.AsEnumerable()
            .Select(product => GetMedicalProductViewModel(product));
        return View(viewModel);
    }

    public MedicalProductViewModel GetMedicalProductViewModel(MedicalProduct product)
    {
        var mapper = new MedicalProductMapper(_db);

        return mapper.GetMedicalProductViewModel(product);            
    }
    public MedicalProduct GetMedicalProduct(MedicalProductViewModel viewModel)
    {
        var mapper = new MedicalProductMapper(_db);

        return mapper.GetMedicalProduct(viewModel);
    }

    //
    // GET: /MedicalSupply/Edit/5

    public ActionResult Edit(int id = 0)
    {
        MedicalProduct medicalProduct = _db.Products.Find(id);
        if (medicalProduct == null)
        {
            return HttpNotFound();
        }

        var viewModel = GetMedicalProductViewModel(medicalProduct);
        return View(viewModel);
    }

    //
    // POST: /MedicalSupply/Edit/5

    [HttpPost]
    [ValidateAntiForgeryToken]
    public ActionResult Edit(MedicalProduct medicalProduct)
    {
        if (ModelState.IsValid)
        {
            _db.Entry(medicalProduct).State = EntityState.Modified;
            _db.SaveChanges();
            return RedirectToAction("Index");
        }

        var viewModel = GetMedicalProductViewModel(medicalProduct);
        return View(viewModel);
    }
}

Stack Trace

[InvalidOperationException: There is already an open DataReader
associated with this Command which must be closed first.]
System.Data.SqlClient.SqlInternalConnectionTds.ValidateConnectionForExecute(SqlCommand
command) +5287423
System.Data.SqlClient.SqlConnection.ValidateConnectionForExecute(String
method, SqlCommand command) +20
System.Data.SqlClient.SqlCommand.ValidateCommand(String method,
Boolean async) +155
System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior
cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String
method, TaskCompletionSource`1 completion, Int32 timeout, Task& task,
Boolean asyncWrite) +82
System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior
cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String
method) +53
System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior
behavior, String method) +134
System.Data.SqlClient.SqlCommand.ExecuteDbDataReader(CommandBehavior
behavior) +41
System.Data.Common.DbCommand.ExecuteReader(CommandBehavior behavior)
+10 System.Data.EntityClient.EntityCommandDefinition.ExecuteStoreCommands(EntityCommand
entityCommand, CommandBehavior behavior) +437

[EntityCommandExecutionException: An error occurred while executing
the command definition. See the inner exception for details.]
System.Data.EntityClient.EntityCommandDefinition.ExecuteStoreCommands(EntityCommand
entityCommand, CommandBehavior behavior) +507
System.Data.Objects.Internal.ObjectQueryExecutionPlan.Execute(ObjectContext
context, ObjectParameterCollection parameterValues) +730
System.Data.Objects.ObjectQuery1.GetResults(Nullable1
forMergeOption) +131
System.Data.Objects.ObjectQuery1.System.Collections.Generic.IEnumerable<T>.GetEnumerator()
+36 System.Linq.Enumerable.Single(IEnumerable1 source) +179 System.Data.Objects.ELinq.ObjectQueryProvider.b_3(IEnumerable1
sequence) +41
System.Data.Objects.ELinq.ObjectQueryProvider.ExecuteSingle(IEnumerable1
query, Expression queryRoot) +59
System.Data.Objects.ELinq.ObjectQueryProvider.System.Linq.IQueryProvider.Execute(Expression
expression) +133
System.Data.Entity.Internal.Linq.DbQueryProvider.Execute(Expression
expression) +123 System.Linq.Queryable.Single(IQueryable1 source,
Expression1 predicate) +287
MvcMedicalStore.Mappers.MedicalProductMapper.GetMedicalProductViewModel(MedicalProduct
source) in c:\Users\Matt\Documents\Visual Studio
2012\Projects\MvcMedicalStore\MvcMedicalStore\Mappers\MedicalProductMapper.cs:28
MvcMedicalStore.Controllers.<>c_DisplayClass1.b_0(MedicalProduct
product) in c:\Users\Matt\Documents\Visual Studio
2012\Projects\MvcMedicalStore\MvcMedicalStore\Controllers\HomeController.cs:28
System.Linq.WhereSelectEnumerableIterator2.MoveNext() +145
ASP._Page_Views_Home_Index_cshtml.Execute() in
c:\Users\Matt\Documents\Visual Studio
2012\Projects\MvcMedicalStore\MvcMedicalStore\Views\Home\Index.cshtml:25
System.Web.WebPages.WebPageBase.ExecutePageHierarchy() +197
System.Web.Mvc.WebViewPage.ExecutePageHierarchy() +119
System.Web.WebPages.StartPage.RunPage() +17
System.Web.WebPages.StartPage.ExecutePageHierarchy() +62
System.Web.WebPages.WebPageBase.ExecutePageHierarchy(WebPageContext
pageContext, TextWriter writer, WebPageRenderingBase startPage) +76
System.Web.Mvc.RazorView.RenderView(ViewContext viewContext,
TextWriter writer, Object instance) +743
System.Web.Mvc.BuildManagerCompiledView.Render(ViewContext
viewContext, TextWriter writer) +382
System.Web.Mvc.ViewResultBase.ExecuteResult(ControllerContext context)
+431 System.Web.Mvc.ControllerActionInvoker.InvokeActionResult(ControllerContext
controllerContext, ActionResult actionResult) +39
System.Web.Mvc.<>c__DisplayClass1a.<InvokeActionResultWithFilters>b__17()
+74 System.Web.Mvc.ControllerActionInvoker.InvokeActionResultFilter(IResultFilter
filter, ResultExecutingContext preContext, Func1 continuation) +388
System.Web.Mvc.<>c_DisplayClass1c.b_19()
+72 System.Web.Mvc.ControllerActionInvoker.InvokeActionResultWithFilters(ControllerContext
controllerContext, IList1 filters, ActionResult actionResult) +303
System.Web.Mvc.Async.<>c__DisplayClass2a.<BeginInvokeAction>b__20()
+155 System.Web.Mvc.Async.<>c__DisplayClass25.<BeginInvokeAction>b__22(IAsyncResult
asyncResult) +184 System.Web.Mvc.Async.WrappedAsyncResult1.End()
+136 System.Web.Mvc.Async.AsyncResultWrapper.End(IAsyncResult asyncResult, Object tag) +56
System.Web.Mvc.Async.AsyncControllerActionInvoker.EndInvokeAction(IAsyncResult
asyncResult) +40
System.Web.Mvc.<>c_DisplayClass1d.b_18(IAsyncResult
asyncResult) +40
System.Web.Mvc.Async.<>c_DisplayClass4.b_3(IAsyncResult
ar) +47 System.Web.Mvc.Async.WrappedAsyncResult1.End() +151
System.Web.Mvc.Async.AsyncResultWrapper.End(IAsyncResult asyncResult,
Object tag) +59
System.Web.Mvc.Async.AsyncResultWrapper.End(IAsyncResult asyncResult,
Object tag) +40
System.Web.Mvc.Controller.EndExecuteCore(IAsyncResult asyncResult) +44
System.Web.Mvc.Async.<>c__DisplayClass4.<MakeVoidDelegate>b__3(IAsyncResult
ar) +47 System.Web.Mvc.Async.WrappedAsyncResult1.End() +151
System.Web.Mvc.Async.AsyncResultWrapper.End(IAsyncResult asyncResult,
Object tag) +59
System.Web.Mvc.Async.AsyncResultWrapper.End(IAsyncResult asyncResult,
Object tag) +40 System.Web.Mvc.Controller.EndExecute(IAsyncResult
asyncResult) +39
System.Web.Mvc.Controller.System.Web.Mvc.Async.IAsyncController.EndExecute(IAsyncResult
asyncResult) +39
System.Web.Mvc.<>c_DisplayClass8.b_3(IAsyncResult
asyncResult) +45
System.Web.Mvc.Async.<>c_DisplayClass4.b__3(IAsyncResult
ar) +47 System.Web.Mvc.Async.WrappedAsyncResult`1.End() +151
System.Web.Mvc.Async.AsyncResultWrapper.End(IAsyncResult asyncResult,
Object tag) +59
System.Web.Mvc.Async.AsyncResultWrapper.End(IAsyncResult asyncResult,
Object tag) +40
System.Web.Mvc.MvcHandler.EndProcessRequest(IAsyncResult asyncResult)
+40 System.Web.Mvc.MvcHandler.System.Web.IHttpAsyncHandler.EndProcessRequest(IAsyncResult
result) +38
System.Web.CallHandlerExecutionStep.System.Web.HttpApplication.IExecutionStep.Execute()
+9628700 System.Web.HttpApplication.ExecuteStep(IExecutionStep step, Boolean& completedSynchronously) +155

Best Answer

You make another request in your select for each of your products. But your products are enumerated so the first datareader is not closed. It is why you have multiple datareaders opened.

public ActionResult Index()
{
    var products = _db.Products.ToArray() // force loading the results from database 
                                           // and close the datareader

    var viewModel = products.Select(product => GetMedicalProductViewModel(product));

    return View(viewModel);
}

Additional: I think you shoul optimize your model creation: you are making the same request (selecting brands) for each products in your database.

To avoid non necessary multiple database roundtrips, you should :

  1. Load your products
  2. Load your brands
  3. Build your models using one product and the brands taken from step 2
Related Topic