Q:

Get Distinct Entries Based On Specific Column in Entity Framework

I have a SQLite table that contains every test result we've run, and I'm looking to write an entity framework query that returns only the most recent test result per project. 
Normally, I'd assume this would be "group by project id, return row with the max updated value," or, alternatively, "sort by date and return first". However, when I try the query, I keep getting Entity Framework "could not be translated" errors. 
Here's what I've tried:
results = await _context.Results
            .Include(x => x.Project)
            .AsNoTracking()
            .OrderByDescending(x => x.Updated)
            .GroupBy(x => x.ProjectId, (x, y) => y.First())
            .ToListAsync();

However, I keep receiving errors that the .First() command could not be translated by Entity Framework. Is there something I'm missing (or, alternatively, a better way to write the query that is more entity framework friendly)?
For reference, here's the operation I'm trying to do in normal SQL: https://thoughtbot.com/blog/ordering-within-a-sql-group-by-clause
I'd prefer to do as much as the work on the server as possible, because there are only a small number of projects, but there could be thousands of results, and I'd rather not do client-side filtering if possible. 
The application is written for ASP.NET Core 3.1, using Entity Framework Core. 
Minor edit: While SQLite is being used for development, the final code will run against SQL Server, hence the desire to do processing server-side.

A:

Try with a subquery instead of a grouping.  Like this:
results = await _context.Results
            .Include(x => x.Project)
            .AsNoTracking() 
            .Where( r => r.Id == _context.Results.Where( rr => rr.ProjectId == r.ProjectID).Max( rr => rr.Id) )
            .ToListAsync();

