title: LINQ for getting the 5 latest records for each distinct name
body: <p>I have been trying to get the 5 most recent records from a database with distinct names from the &quot;Name&quot; Column. Meaning I want the 5 latest records for each of the unique names in the table.</p>
  <p>Here is a sample table:</p>
  <pre><code>id  |   Name   |  Status  | Start      | End     &lt;br /&gt;
  1   |  Bob     |  Pass    | 2020-01-01 | 2020-01-01&lt;br /&gt;
  2   |  Chris   |  Pass    | 2020-01-01 | 2020-01-02&lt;br /&gt;
  3   |  James   |  Fail    | 2020-01-01 | 2020-01-03&lt;br /&gt;
  4   |  Bob     |  Pass    | 2020-01-01 | 2020-01-04&lt;br /&gt;
  5   |  Chris   |  Fail    | 2020-01-01 | 2020-01-05&lt;br /&gt;
  6   |  Bob     |  Pass    | 2020-01-01 | 2020-01-06&lt;br /&gt;
  7   |  Bob     |  Fail    | 2020-01-01 | 2020-01-07&lt;br /&gt;
  8   |  Bob     |  Fail    | 2020-01-01 | 2020-01-08&lt;br /&gt;
  9   |  Chris   |  Pass    | 2020-01-01 | 2020-01-09&lt;br /&gt;
  10  |  Bob     |  Pass    | 2020-01-01 | 2020-01-10&lt;br /&gt;
  </code></pre>
  <p>I would expect the latest 5 Bob records (out of the 6 ), the 3 Chris records, and the one James record to be returned.</p>
  <p>I have tried to methods so far:</p>
  <ol>
  <li>Breaking this into two different operations:Querying for the Distinct names, then query based on the name, get the latest 5 records by endDate and append to a list. With this method I was able to do the first query correctly. I was printing out the three distinct name (Bob, Chris, James). However, everytime I did a query with those names and getting the 5 most recent records, and it would come back empty for all three names. Any printing I tried to do said the variable name was void according to Visual Studio 2019...SO I tried method 2</li>
  </ol>
  <pre><code>// GET: api/Student/latestRecords
  [HttpGet(&quot;latestRecords&quot;)]
  public async Task&lt;ActionResult&lt;IEnumerable&lt;Student&gt;&gt;&gt; GetLatestRecordsOnAllStudent()
  {
      var distinctStudentNames = _context.Students.Select(x =&gt; x.name).Distinct();
  
      IQueryable&lt;Student&gt; allRecords = new Student[] { }.AsQueryable();
  
      foreach (string studentName in distinctStudentNames)
      {
          var newList = _context.Students.OrderByDescending(x =&gt; x.endTime).DistinctBy(y =&gt; y.name).Select( z =&gt; z).Take(5);
  
          allRecords.Concat(newList);
      }
              
      return allRecords.ToList();*/
  }
  </code></pre>
  <ol start="2">
  <li>Using a single LINQ query. With this method I was able to get 3 distinct records based on name, but I could not get more records than that.</li>
  </ol>
  <pre><code>// GET: api/Student/latestRecords
  [HttpGet(&quot;latestRecords&quot;)]
  public async Task&lt;ActionResult&lt;IEnumerable&lt;Student&gt;&gt;&gt; GetLatestRecordsOnAllStudents()
  {
      var distinctStudentsNames = _context.Students.DistinctBy(x =&gt; x.name).OrderByDescending(x =&gt; x.endTime).Select(z =&gt; z).Take(5).ToList();
  
      return distinctStudentsNames;
  }
  </code></pre>
  <p>I would love it if method 2 would work, but I feel like I might be stepping on my own toes trying to do it all in one single call. If anyone has some advice, it would be greatly appreciated.</p>