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

Question

This question is related to this:

My repository method has this code:

 public IEnumerable<ApplicationPositionHistory> GetApplicationPositionHistories(int applicantId, int positionId)
        {
            return context.ApplicationsPositionHistory.Where(d => d.applicantPosition.ApplicantID == applicantId && d.applicantPosition.PositionID == positionId).Include(o => o.applicantPosition) ;
        }

My Html has this code:

@foreach (var item in Model) {
    <tr>
        <td>
            @Html.DisplayFor(modelItem => item.applicantPosition.Applicant.name)
        </td>
        <td>
            @Html.DisplayFor(modelItem => item.applicantPosition.Position.name)
        </td>

The full exception is:

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

It was throw in the first line of the HTML @Html.DisplayFor(modelItem => item.applicantPosition.Applicant.name)

Solution 1

Quick solution :

public IEnumerable<ApplicationPositionHistory> GetApplicationPositionHistories(int applicantId, int positionId)
    {
        return context.ApplicationsPositionHistory.Where(d => d.applicantPosition.ApplicantID == applicantId && d.applicantPosition.PositionID == positionId).Include(o => o.applicantPosition).ToList() ;
    }

If you want to know, why this fixing your problem, read about how LINQ and deffered execution works. In few words - if you dont "force" execution of the select by "enumerating" query by ToList, it is in fact executed too late - in view. And this is causing trouble with other queries which want to use same connection.

Solution 2

Have you tried adding MultipleActiveResultSets=true; to your connection string?

Solution 3

This error happens when a new query is going to be executed while you're in inside another query. Consider you have something like this in your view

@Html.DisplayFor(modelItem => item.Device.Name)

and in your Device model you have

    public string Name
    {
        get
        {
            return String.Format("{0} {1}", Brand.BrandName, Model.ModelName);
        }
    }

then since for evaluating Device.Name it requires to query its Brand and Model it will become query inside query and so the solution is to enable MutlipleActiveResultSets in your database connection string as follows:

    <add name="MyDBContext" connectionString="Data Source=.;Initial Catalog=mydb;Integrated Security=True;MultipleActiveResultSets=True" providerName="System.Data.SqlClient" />

Solution 4

Generally do not use EF object in view, but create a POCO object for the view model and map the query result on the view model. EF do not execute the query in your repository method because the query are not executed at definition time but only when you try to access the data. In your view you are using the same query many time and this is not correct.

If you want to access the list of object returned by your repository method, use toList

Solution 5

The real problem if that you are Lazy Loading the Position reference from the ApplicantPosition entity before the query ends this execution. If you want to keep the deferred execution on that scenario you can eager load the Position reference on your query like this:

Include(o => o.applicantPosition.Select(a => a.Position));

and on your GetApplicationPositionHistories keeps returning the IEnumerable.

The other solution is to actually run the query on the GetApplicationPositionHistories method calling the ToList() or ToArrray() methods on the query.

View more solutions