title: EFCore: Query a related entity based on the maximum value in a field in the related entity
body: <p>I need to query a related entity based on the maximum value in a field in the related entity, and then display results from that item.</p>
  
  <p>For example, the models are defined:</p>
  
  <pre><code>public class Student
  {
      public int StudentID {get; set;}
      public string Name {get; set;}
      public ICollection&lt;ReportCard&gt; ReportCards {get; set;}
  }
  
  public class ReportCard
  {
      public int ReportCardID {get; set;}
      public int ProjectID { get; set; }
      public Project Project { get; set; }
      public int Result {get; set;}
      public int Comment {get; set;}
      public DateTime PublishDate {get; set;}
  }
  </code></pre>
  
  <p>In the razor controller:</p>
  
  <pre><code>public class LatestResultsModel : PageModel
  {
      ...
      public IList&lt;Student&gt; Students {get; set;}
      public async Task&lt;IActionResult&gt; OnGetAsync()
      {
          Students = await _context.Student
                                   .Include(student =&gt; student.ReportCard)
                                   .ToListAsync();
      }
  }
  </code></pre>
  
  <p>In the razor view:</p>
  
  <pre><code>@foreach (Student student in Model.Students)
  {
      &lt;p&gt;@student.Name&lt;/p&gt;
      &lt;p&gt;@student.ReportCard.Max(i =&gt; i.PublishDate).Result.ToString()&lt;/p&gt;
  }
  </code></pre>
  
  <p>After the Max statement I cannot query the other fields.
  I've tried a few things to achieve this outcome of filtering the related data.</p>
  
  <p><a href="https://github.com/aspnet/EntityFrameworkCore/issues/1833" rel="nofollow noreferrer">Filtered Includes are not supported.</a></p>
  
  <p>Is there some type of join that could achieve this outcome?</p>
  
  <p>It also doesn't handle the situation when a Student has no ReportCard. InvalidOperationException: Nullable object must have a value.</p>