Get Distinct Entries Based On Specific Column in Entity Framework

c# entity-framework entity-framework-core sql sqlite

Question

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.

1
0
3/28/2020 6:29:49 AM

Accepted Answer

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();
1
3/28/2020 2:52:30 PM

Popular Answer

While this isn't portable, here's how this can be done using SQLite-compatable SQL and Entity Framework:

results = await _context.Results
    .FromSqlRaw("SELECT Results.* FROM (SELECT Id, ProjectId, MAX(Updated) as Updated " +
    "FROM Results GROUP BY ProjectId) as latest_results " +
    "INNER JOIN Results ON Results.Id = latest_results.Id")
    .Include(x => x.Project) //not required for question but useful
    .OrderBy(x => x.Project.Name)
    .AsNoTracking()
    .ToListAsync();

If someone has a way to do this in pure LINQ/EF, but still perform the query server-side, I'll happily mark that as the answer, since this is dependent on the exact SQL dialect used.



Related Questions





Related

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow