CODEWITHSUNDEEP
asp.net-mvc
pensum
pensum

Reputation: 1019

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

I have an Index.cshtml page with a table that contains a foreign key from another table. I have added a search feature in my index but when searching, it throws this exception :

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

I have ran the debugger and it looks like it is going in my ActionResult just fine but when it tries to load the view, it crashes at line 11. I suspect that it's because the item is a foreign key in my db (which is represented as another class in the models) since I have added the search feature in other indexes and it works. I have commented the lines 11 and 12 and the exception is not happening anymore.

I am coming to you because even though I seem to have found the problem, I do not really understand why this exception happens and how to fix it. Any help would be appreciated.

PS : I have used the this code from the Microsoft Docs for the search feature.

1    @foreach (var item in Model)
2    {
3       <tr>
4            <td>
5                @Html.DisplayFor(modelItem => item.nom)
6            </td>
7            <td>
8                @Html.DisplayFor(modelItem => item.adresse)
9            </td>
10           <td>
11             //   @Html.DisplayFor(modelItem => item.responsable.prenom)
12             //   @Html.DisplayFor(modelItem => item.responsable.nom)
13           </td>
14           <td>
15                @Html.DisplayFor(modelItem => item.telephone)
16           </td>
17        </tr>
18    }

My Controller

// GET: cinemas
public ActionResult Index(string searchString)
{
    IndexCinema_ViewModel viewModel = new IndexCinema_ViewModel();

    if (!String.IsNullOrEmpty(searchString))
    {
        viewModel.Cinemas = db.cinemas.Where(s => s.nom.Contains(searchString)
                || s.responsable.nom.Contains(searchString)
                || s.responsable.prenom.Contains(searchString));
        ViewBag.searchRequest = true;
    }
    else
    {
        viewModel.Cinemas = db.cinemas.Include(c => c.responsable);
        ViewBag.searchRequest = false;
    }

    ModelState.Clear();
    return View(viewModel);
}

Upvotes: 0

Views: 412

Answers (1)

Dai
Dai

Reputation: 155250

Never send an IQueryable<T> or non-materialized IEnumerable<T> outside a controller action!: Notice how in the example you linked to on Microsoft's website, the code calls ToList() prior to returning View(model).

You need to materialize your query to a List<T> in your Action so that the DbContext can be disposed-of safely and not leave dangling connections open:

viewModel.Cinemas = await db.cinemas
    .Include(c => c.responsable)
    .ToListAsync();

Upvotes: 2

Related Questions