1

Very new to ASP.NET MVC and Entity Framework.

I'm trying to set up a database with three tables, when I run my code and go to the page "Tickets", I get the following exception:

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

Class 1

public class Ticket
{
    [Key]
    public int ID { get; set; }
    public string Description { get; set; }
    [ForeignKey("Practice")]
    public int PracticeID { get; set; }

    public string Contact { get; set; }
    public string Category { get; set; }

    //insert Support type using ViewBag (Support type listed in Models.Practices)
    [DataType(DataType.DateTime)]
    [DisplayFormat(DataFormatString = "{0:dd-MM-yyyy}", ApplyFormatInEditMode = true)]
    public DateTime Due { get; set; }

    [DataType(DataType.DateTime)]
    [DisplayFormat(DataFormatString = "{0:dd-MM-yyyy}", ApplyFormatInEditMode = true)]
    public DateTime TimeLogged { get; set; }

    [ForeignKey("Consultant")]
    public int ConsultantID { get; set; }
    public string ConsultantName { get; set; }
    public string Status { get; set; }

    public virtual Practice Practice { get; set; }
    public virtual Consultant Consultant { get; set; }
}

Class 2:

public class Practice
{
    [Key]
    public int PracticeID { get; set; }
    [Display(Name = "Practice Name")]
    public string PracName { get; set; }
    [Display(Name = "Practice Number")]
    public int PracNumber { get; set; }
    public string Contact { get; set; }

    [Display(Name = "Support Type")]
    public string Support { get; set; }
    public string Tel { get; set; }
    public string Cell { get; set; }

    [DataType(DataType.EmailAddress)]
    public string Email { get; set; }
    public string Address { get; set; }
    public virtual List<Ticket> Ticket { get; set; }
}

Class 3:

public class Consultant
{
    [Key]
    public int ConsultantID { get; set; }
    public string Name { get; set; }

    [DataType(DataType.Password)]
    public string Password { get; set; }

    public string Role { get; set; }
    public virtual List<Ticket> Ticket { get; set; }
}

I have a search function in my TicketsController:

public ActionResult Index(string searchString, string Consultants)
{
        var UserLst = new List<string>();

        var UserQry = from d in db.Consultants
                      orderby d.Name
                      select d.Name;

        UserLst.AddRange(UserQry.Distinct());
        ViewBag.User = new SelectList(UserLst);

        var tickets = from m in db.Ticket
                      select m;

        if (!String.IsNullOrEmpty(searchString))
        {
            tickets = tickets.Where(s => s.Description.Contains(searchString));
        }

        if (!string.IsNullOrEmpty(Consultants))
        {
            tickets = tickets.Where(x => x.ConsultantName == Consultants);
        }

        return View(tickets);
    }

The error seems to be coming from the HTML code in the INDEX.

@model IEnumerable<MAD.Models.Ticket>

@{
    ViewBag.Title = "Index";
}

<h2>Index</h2>

<p>
    @Html.ActionLink("Create New", "Create")
</p>




<p>
    User: @Html.DropDownList("User", "All")
    Description: @Html.TextBox("SearchString") <br />
    <input type="submit" value="Search" />
</p>


<table class="table">
    <tr>
        <th>
            @Html.DisplayNameFor(model => model.Consultant.Name)
        </th>
        <th>
            @Html.DisplayNameFor(model => model.Practice.PracName)
        </th>
        <th>
            @Html.DisplayNameFor(model => model.Description)
        </th>
        <th>
            @Html.DisplayNameFor(model => model.Contact)
        </th>
        <th>
            @Html.DisplayNameFor(model => model.Category)
        </th>
        <th>
            @Html.DisplayNameFor(model => model.Due)
        </th>
        <th>
            @Html.DisplayNameFor(model => model.TimeLogged)
        </th>
        <th>
            @Html.DisplayNameFor(model => model.Status)
        </th>
        <th></th>
    </tr>

@foreach (var item in Model) {
    <tr>
        <td>
            @Html.DisplayFor(modelItem => item.Consultant.Name)--ERROR CODE
        </td>
        <td>
            @Html.DisplayFor(modelItem => item.Practice.PracName)
        </td>
        <td>
            @Html.DisplayFor(modelItem => item.Description)
        </td>
        <td>
            @Html.DisplayFor(modelItem => item.Contact)
        </td>
        <td>
            @Html.DisplayFor(modelItem => item.Category)
        </td>
        <td>
            @Html.DisplayFor(modelItem => item.Due)
        </td>
        <td>
            @Html.DisplayFor(modelItem => item.TimeLogged)
        </td>
        <td>
            @Html.DisplayFor(modelItem => item.Status)
        </td>
        <td>
            @Html.ActionLink("Edit", "Edit", new { id=item.ID }) |
            @Html.ActionLink("Details", "Details", new { id=item.ID }) |
            @Html.ActionLink("Delete", "Delete", new { id=item.ID })
        </td>
    </tr>
}
</table>
2
  • It's most likely because you're querying db.Consultants and then db.Ticket and in between your connection is never disposed of. Commented May 18, 2016 at 21:33
  • typically the query is execute on other dll or layer the problem is the scope try with "using" for dispose after of retrive data Commented May 18, 2016 at 23:46

1 Answer 1

1

You have passed in an IQueryable from your controller as your model - called tickets in the controller method. When you start your for each loop on the model, EF starts retrieving results through the connection, but it doesn't actually finish using the connection for this until the end of the loop. The line which is causing the error is trying to access the related Consultant property, which triggers EF to try to load that from the DB, but that causes an exception because you are still in the loop and it is still using the connection to retrieve the tickets query.

The easiest way around this is to force EF to retrieve the results before the loop. I would go for changing the final line in the controller to View (tickets.ToArray) or similar.

I'm not sure, but enabling multiple active result sets might also fix this.

Sign up to request clarification or add additional context in comments.

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.