C# Entity Framework: There is already an open DataReader associated with this Connection which must be closed first

Question

I'm working on a ASP.NET MVC3 application and I've created a database in MySQL 5.5 which contains a company table having a one-to-many relationship with a contacts table.

table Bedrijf (with navigation property "contacts")

table Contact

Since I had to take over this database from a currently running site I generated a Entity Model based on that database and I wrote the following code to display a list of companies (grouped by status), mentioning the number of contacts in that company:

CompanyRepository.cs

...

public IQueryable<Bedrijf> getCompaniesByStatus(int status)
    {
        return entities.Bedrijven.Where(c => c.bedrijf_status == status).OrderBy(c => c.bedrijf_naam);
    }

...

View calling 3 partial Views

@{Html.RenderPartial("ucCompaniesByStatus", Model.newCompanies, (new ViewDataDictionary { { "Titel", "Nieuwe bedrijven" } }));}

<br />

@{Html.RenderPartial("ucCompaniesByStatus", Model.activeCompanies, (new ViewDataDictionary { { "Titel", "Actieve bedrijven" } }));}

<br />

@{Html.RenderPartial("ucCompaniesByStatus", Model.inActiveCompanies, (new ViewDataDictionary { { "Titel", "Niet actieve bedrijven" } }));}

Partial View

@model IEnumerable<xxx.Models.Bedrijf>

<table id="companytable">
    <tr>
        <th id="thtitle">
            @ViewData["Titel"]
        </th>
        <th id="thactions"></th>
    </tr>

@foreach (var item in Model)
{
    <tr>
        <td>
            @Html.ActionLink(@item.bedrijf_naam, "CompanyDetails", new { id = item.bedrijf_id }) 
            (@item.contacts.Count contact(en))



        </td>
        <td id="actions">
            @Html.ActionLink("Edit", "CompanyEdit", new { id=item.bedrijf_id }) |
            @Html.ActionLink("Details", "CompanyDetails", new { id = item.bedrijf_id }) |
            @Html.ActionLink("Delete", "Delete", new { id = item.bedrijf_id })
        </td>
    </tr>
}
</table>

In my list of companies, I would like to display the number of contacts assigned to that company but I got the following Error:

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

When go to my .edmx file and set Lazy Loading Enabled : False I'm able to get a result (But the count on my contacts is not working (I get 0), assuming my related contacts are not loaded now.):

How Can I get this working with Lazy Loading Enabled? My beginner ASP.NET (MVC) skills don't bring me to a solution at the moment.

Adding MultipleActiveResultSets=True; in the web.config connectionstring is pointed out as solution often, but no difference in my case.

Tried the .Include in my CompanyRespository while having lazy loading set to False, but I think I didn't do that correctly since I'm not familiar witht he syntax.

This description makes also sense;

It is not about closing connection. EF manages connection correctly. My understanding of this problem is that there are multiple data retrieval commands executed on single connection (or single command with multiple selects) while next DataReader is executed before first one has completed the reading. The only way to avoid the exception is to allow multiple nested DataReaders = turn on MultipleActiveResultSets. Another scenario when this always happens is when you iterate through result of the query (IQueryable) and you will trigger lazy loading for loaded entity inside the iteration.

but no idea how I should fix this problem in my code with this information. Where/How use @item.contacts.Count to show the number of contacts?

Thanks in advance.

Solution 1

Try using this:

public IQueryable<Bedrijf> getCompaniesByStatus(int status)
{
    return entities.Bedrijven
                   .Include("contacts")
                   .Where(c => c.bedrijf_status == status)
                   .OrderBy(c => c.bedrijf_naam);
}

I think MySql connector probably doesn't support multiple active result sets and because of that the setting in connection string didn't help you.

Solution 2

I had similar Issue. Noticed that was using IEnumerable collection and was calling another function, where was querying database. Since it was IEnumerable collection, reader was open. Changed IEnumerable to list to resolve the issue.

Solution 3

The MySQL connector does not support MultipleActiveResultSets, so that connection string modification will not work.

To get around the issue, in your controller, simply add the .ToList() method to wherever your data is queried.. For example...

public ActionResult Create()
{
    ViewBag.PossibleCompanies = context.Companies.ToList();
    return View();
} 

Comments

[tortuga]

Strange, I tried that before and it didn't work, and now it does. :) Thank you!

[badMonkey]

I accomplished what @AbbsHere suggested on my version of this problem by simply adding a .ToList() to the end of my statement.