There is Already an open DataReader that must be closed first

Question

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

Answer 1

EDIT: As you are already aware of the multiple result sets flag, as pointed out in the comments, I thought I'd change this answer into a more useful one.

Something that would solve your issue, and is also just a really good practice for getting data from the context that you are not going to edit, is to explicitly tell EntityFramework to not track the entities, effectively rendering them in the context as read-only objects that will never be updated back to the database.

This is really easy to do: just use 'AsNoTracking()'. All you basically need is:

var brands = _db.Brands.AsNoTracking().ToList();

Now you can use this list to set that as a lookup on your product viewmodel, and you can use it also for getting the brandName for that specific product's viewmodel. Just expand your GetMedicalProductViewModel with a list of brands like this:

GetMedicalProductViewModel(MedicalProduct source, IEnumerable<Brand> brands)

and then use brands instead of your _db.Brands and you're good to go:

var brands = _db.Brands.AsNoTracking().ToList();
var viewModel = _db.Products.AsNoTracking().Select(product => GetMedicalProductViewModel(product, brands));

return View(viewModel);

Additionally, note that you are using the same viewmodel for both editing and your list. You can see in this case that is very inefficient, as each of your product on the index page gets its own copy of the brands list included, which can eventually become a lot of extra data in your index view that you don't actually need. So I strongly recommend using a MedicalProductIndexViewModel that does not have BrandSelectListItem (and that itself should probably be pluralized to Items).

This can really make a big difference - if there are 10 brands, then that's 500 key value pairs if you have a page size of 50, which may well be close to 10 times as much data as the product index really needed. If there are 100 brands ... you get the picture.

If you are not using BrandName in the ProductIndex, you can omit that too and make it even more efficient as that part of the query can be skipped as well.

Also, instead of having a GetMedicalProductViewModel, I typically just give my viewmodel constructor arguments and populate from there.

And finally, thinks like the brands lookup can also be populated on demand using Ajax calls, which is generally also more efficient, as it can load while the page is already there for the user to start working with, can work with async search while typing a brand name, etc.

Answer 2

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