LINQ for getting the 5 latest records for each distinct name

I have been trying to get the 5 most recent records from a database with distinct names from the “Name” Column. Meaning I want the 5 latest records for each of the unique names in the table.

Here is a sample table:

id  |   Name   |  Status  | Start      | End     <br />
1   |  Bob     |  Pass    | 2020-01-01 | 2020-01-01<br />
2   |  Chris   |  Pass    | 2020-01-01 | 2020-01-02<br />
3   |  James   |  Fail    | 2020-01-01 | 2020-01-03<br />
4   |  Bob     |  Pass    | 2020-01-01 | 2020-01-04<br />
5   |  Chris   |  Fail    | 2020-01-01 | 2020-01-05<br />
6   |  Bob     |  Pass    | 2020-01-01 | 2020-01-06<br />
7   |  Bob     |  Fail    | 2020-01-01 | 2020-01-07<br />
8   |  Bob     |  Fail    | 2020-01-01 | 2020-01-08<br />
9   |  Chris   |  Pass    | 2020-01-01 | 2020-01-09<br />
10  |  Bob     |  Pass    | 2020-01-01 | 2020-01-10<br />

I would expect the latest 5 Bob records (out of the 6 ), the 3 Chris records, and the one James record to be returned.

I have tried to methods so far:

1. 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

// GET: api/Student/latestRecords
[HttpGet("latestRecords")]
public async Task<ActionResult<IEnumerable<Student>>> GetLatestRecordsOnAllStudent()
{
    var distinctStudentNames = _context.Students.Select(x => x.name).Distinct();

    IQueryable<Student> allRecords = new Student[] { }.AsQueryable();

    foreach (string studentName in distinctStudentNames)
    {
        var newList = _context.Students.OrderByDescending(x => x.endTime).DistinctBy(y => y.name).Select( z => z).Take(5);

        allRecords.Concat(newList);
    }
            
    return allRecords.ToList();*/
}

2. 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.

// GET: api/Student/latestRecords
[HttpGet("latestRecords")]
public async Task<ActionResult<IEnumerable<Student>>> GetLatestRecordsOnAllStudents()
{
    var distinctStudentsNames = _context.Students.DistinctBy(x => x.name).OrderByDescending(x => x.endTime).Select(z => z).Take(5).ToList();

    return distinctStudentsNames;
}

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.

Answer

Since you are using EF Core, you are limited in what you can do with GroupBy. Instead, you can replace GroupBy with a sub-query that returns which records you want for each name:

var allRecords = from s in _context.Students
                 where (from s2 in _context.Students
                        where s2.name == s.name
                        orderby s2.endTime descending
                        select s2.id).Take(5).Contains(s.id)
                 orderby s.name, s.endTime descending
                 select s;

Or, if you prefer the fluent version:

var allRecords2 = _context.Students.Where(s => _context.Students.Where(s2 => s2.name == s.name)
                                                       .OrderByDescending(s2 => s2.endTime)
                                                       .Select(s2 => s2.id)
                                                       .Take(5)
                                                       .Contains(s.id))
                                    .OrderBy(s => s.name)
                                    .ThenByDescending(s => s.endTime);