Entity Framework: There is already an open DataReader associated with this command, which should 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> 

Full exception:

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

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

Answer 1

Fast decision:

 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 is a fix for your problem, read about how LINQ and release execution work . In a few words - if you are not "forced" to make the choice of "listing" the ToList request, it actually runs too late - in sight. And this causes problems with other requests that want to use the same connection.

Answer 2

You tried to add MultipleActiveResultSets=true; to the connection string?

Answer 3

This error occurs when a new request is executed when you are inside another request. Do 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, for evaluating Device.Name you need to request its Brand and Model, it will become a request inside the request, and therefore the solution should include MutlipleActiveResultSets in the database connection string as follows:

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

Answer 4

As a rule, do not use the EF object in the view, but create a POCO object for the view model and map the query result to the view model. EF does not execute the request in your repository method, because the request is not executed during the definition, but only when trying to access the data. In your opinion, you use the same query many times, and this is not true.

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

Answer 5

The real problem is if you are Lazy Load the Position link from the ApplicantPosition object before the request completes this execution. If you want to keep pending execution in this scenario, you can request a link to a position in your request as follows:

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

and on your GetApplicationPositionHistories continues to return IEnumerable.

Another solution is to actually run the request in the GetApplicationPositionHistories method by calling the ToList () or ToArrray () methods in the request.