1

I am a beginner and learning Asp.Net MVC 5 web development. In one of my views I am getting exception as

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

Model:

public class CoverLetter
{
    [Key]
    public int CoverLetterId { get; set; }

    [Required]
    [Display(Name = "Cover Letter")]
    [StringLength(255)]
    public string CoverLetterName { get; set; }

    [Display(Name = "Company Name")]
    [Required]
    public int CompanyId { get; set; }
    public virtual Company Company { get; set; }

    [Required]
    public string CandidateId { get; set; }
    public virtual ApplicationUser Candidate { get; set; }
}

View:

@model IEnumerable<Bridge.Models.CoverLetter>
@using Bridge.ViewModels

<div class="panel panel-default">
    <table class="table">
        <thead>
            <tr>
                <th>
                    @Html.DisplayNameFor(model => model.CoverLetterName)
                </th>
                <th>
                    @Html.DisplayNameFor(model => model.Company.CompanyName)
                </th>
            </tr>
        </thead>
            foreach (var item in Model)
            {
                <tbody>
                    <tr>
                        <td>
                            @Html.DisplayFor(modelItem => item.datetime)
                        </td>
            @*In below code exception comes*@
                        <td>
                            @Html.DisplayFor(modelItem => item.Company.CompanyName)
                        </td>
                          </tr>
                </tbody>
            }
    </table>
</div>

Controller:

public ActionResult CoverLetterCenter()
{
    var candidateId = User.Identity.GetUserId();
    var coverLetterList = _context.CoverLetters
        // .Include("Company")
        .Where(r => r.CandidateId == candidateId).OrderByDescending(r => r.datetime);

    return View(coverLetterList);
}

Point 1:

If I add .Include("Company") in my controller action then the exception goes away. Can someone please tell me about this behavior.

Point 2:

Also note that, when I debug and hover Over the coverLetterList variable, then it always have the Company variable populated with the data, irrespective of whether I have added .Include("Company") or not. Then why am I getting exception.

Community
  • 1
  • 1
Unbreakable
  • 7,776
  • 24
  • 90
  • 171
  • 1
    Because without the `.Include()`, it means that `@Html.DisplayNameFor(m => m.Company.CompanyName)` needs to open a connection to get the `Company` (but your query has already opened one) –  Sep 10 '17 at 03:42
  • But when I hover over I see that company value is already there in controllerAction's LINQ Query, even when I do not use ".Include". then why do the connection needs to be opened? :/ – Unbreakable Sep 10 '17 at 03:44
  • 1
    I assume that is because your 'expanding the results' which actually executes the query. Note that your query returns `IQueryable` and its not iterated until the code in your view (the `foreach` loop is actually executed. If for example you used `.ToList()` then it would be executed immediately. –  Sep 10 '17 at 03:47
  • @StephenMuecke: Understood. So, If I do not `Include("Company")` and add `.ToList` at the end of my query, then I will not get any exception right? – Unbreakable Sep 10 '17 at 03:54
  • I think you should test that yourself :) –  Sep 10 '17 at 03:55
  • Yes, just now tested. No exception. :) Awesome! – Unbreakable Sep 10 '17 at 03:57
  • Let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/154048/discussion-between-unbreakable-and-stephen-muecke). – Unbreakable Sep 10 '17 at 03:58

1 Answers1

1

Entity Framework and LINQ in general use Lazy evaluation for Enumerable collections. For the controller code there is no need to evaluate the enumeration since it is not being used until the code gets to the view rendering. By debugging and hovering over the data in the debug window the evaluation is performed and thus you see the company data. You can add a single method chain to fix this.

public ActionResult CoverLetterCenter()
{
    var candidateId = User.Identity.GetUserId();
    var coverLetterList = _context.CoverLetters
        // .Include("Company")
        .Where(r => r.CandidateId == candidateId).OrderByDescending(r => r.datetime).ToList();

    return View(coverLetterList);
}
Aaron Roberts
  • 1,342
  • 10
  • 21
  • so AsEnumerable and ToList are same thing? – Unbreakable Sep 10 '17 at 04:04
  • Actually no, and my original answer was wrong. `AsEnumerable()` will still defer the execution of the query but any following statements will not be converted using LINQ to Entities. In this case you want to execute the query in the controller so you will want to use .ToList(). There is a nice writeup here https://stackoverflow.com/questions/17968469/whats-the-differences-between-tolist-asenumerable-asqueryable – Aaron Roberts Sep 10 '17 at 04:08