title: Call Query Method From Loop Within View
body: <p>I am relatively new to using ASP.NET MVC so forgive my ignorance if I am overlooking something simple here, but after a fair deal of research I have still found no solid solution.</p>
  
  <p>I am passing a list of projects from my controller into my view of type: IEnumerable Project:</p>
  
  <pre><code>IEnumerable&lt;Project&gt; Projects = ViewBag.Projects;
  </code></pre>
  
  <p>Then looping through each project and populating a Datatable with the information. The only trouble is to get data for two of the columns in the Datatable I need to make a second DB query from within the loop. </p>
  
  <p>I have created a @functions section to call a method in my repository which should return a list of archives related to any particular project:</p>
  
  <p><strong>View Top</strong></p>
  
  <pre><code>@using stwintranet.Areas.PQP.Models
  @model Year
  
  @{
      Layout = "~/Views/Shared/_Layout.cshtml";
      IEnumerable&lt;Project&gt; Projects = ViewBag.Projects;
      IEnumerable&lt;Year&gt; Years = ViewBag.Years;
      String year = ViewBag.URLYear;
  } 
  @functions
  {
      private readonly IArchive_ElectronicRepository Archive_ElectronicRepository;
  
      public IEnumerable&lt;Archive_Electronic&gt; GetListArchives(int pid)
      {
          return Archive_ElectronicRepository.GetListArchives(pid);
      }
  } 
  </code></pre>
  
  <p><strong>View Main:</strong> </p>
  
  <pre><code>&lt;table id="dt_basic" class="table table-striped table-bordered table-hover" width="100%"&gt;
      &lt;thead&gt;
          &lt;tr&gt;
              &lt;th&gt;Project Year&lt;/th&gt;
              &lt;th&gt;Project Number&lt;/th&gt;
              &lt;th&gt;Project Title&lt;/th&gt;
              &lt;th&gt;Client&lt;/th&gt;
              &lt;th&gt;Site Address&lt;/th&gt;
              &lt;th&gt;Director&lt;/th&gt;
              &lt;th&gt;Archive Status&lt;/th&gt;
              &lt;th&gt;Primary Location&lt;/th&gt;
              &lt;th&gt;Location&lt;/th&gt; &lt;!-- Requires 2nd Query --&gt;
              &lt;th&gt;Documents&lt;/th&gt; &lt;!-- Requires 2nd Query --&gt;
          &lt;/tr&gt;
      &lt;/thead&gt;
      &lt;tbody&gt;
          @foreach (var project in Projects)
          {
              //Get a list of archived projects
              IEnumerable&lt;Archive_Electronic&gt; ArchiveProjects = GetListArchives(project.ProjectID);
              string Docs = "";
              string Locs = "";
              foreach (var archive in ArchiveProjects)
              {
                  //Write List of Docs
                  if (archive.DocumentType == 1)
                  {
                      Docs = Docs + "Dwg ";
                  }
                  else if (archive.DocumentType == 2)
                  {
                      Docs = Docs + "Doc ";
                  }
                  else if (archive.DocumentType == 3)
                  {
                      Docs = Docs + "Eml ";
                  }
                  //Write List of Locations
                  if (archive.Location == 2)
                  {
                      Locs = Locs + "Dub ";
                  }
                  else if (archive.Location == 3)
                  {
                      Locs = Locs + "Gal ";
                  }
                  else if (archive.Location == 4)
                  {
                      Locs = Locs + "Lon ";
                  }
                  else if (archive.Location == 6)
                  {
                      Locs = Locs + "Cor ";
                  }
              }
  
  
              &lt;tr id="@project.ProjectNumber" onclick="showProject(this.id)"&gt;
                  &lt;td&gt;@project.Year&lt;/td&gt;
                  &lt;td&gt;@project.ProjectNumber&lt;/td&gt;
                  &lt;td&gt;@project.ProjectTitle&lt;/td&gt;                                  
                  &lt;td&gt;@project.Organisation.Organisation1&lt;/td&gt;
                  &lt;td&gt;@project.SiteAddress1&lt;/td&gt;
                  &lt;td&gt;@project.ProjectDirector&lt;/td&gt;
                  &lt;td&gt;Archive Status will go here&lt;/td&gt;
                  &lt;td&gt;@project.SiteArea&lt;/td&gt;
                  &lt;td&gt;@Locs&lt;/td&gt;
                  &lt;td&gt;@Docs&lt;/td&gt;
              &lt;/tr&gt;
  
          }
  
  
      &lt;/tbody&gt;
  &lt;/table&gt;
  </code></pre>
  
  <p><strong>Method located within Repository:</strong></p>
  
  <pre><code>public IEnumerable&lt;Archive_Electronic&gt; GetListArchives(int id)
  {
      return context.Archive_Electronic.Where(x =&gt; x.Project == id).DefaultIfEmpty();
  }
  </code></pre>
  
  <p><strong>Controller:</strong></p>
  
  <pre><code>// GET: PQP/Stage9/ElectronicArchiveReport
  public ActionResult ElectronicArchiveReport(string id)
  {
      ViewBag.Years = YearRepository.GetYears();
      ViewBag.Projects = ProjectRepository.GetListProjects(id);
      ViewBag.URLYear = id;
      return View();
  }
  </code></pre>
  
  <p>With this code as is I get a:</p>
  
  <pre><code>System.NullReferenceException: Object reference not set to an instance of an object.
  </code></pre>
  
  <p>for return located in view:</p>
  
  <pre><code>return Archive_ElectronicRepository.GetListArchives(pid);
  </code></pre>
  
  <p>located in @functions, I know you more than likely shouldn't call the DB like this which is probably why I am getting the null exception error but I am at a loss here and can't really find a proper way of doing this.</p>