Get Distinct Entries Based On Specific Column in Entity Framework

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.

score 1 · Accepted Answer · answered Mar 28 '20 at 14:52

1

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();

answered Mar 28 '20 at 14:52

David Browne - Microsoft

80,331
6
39
67

This works perfectly, and appears to generate reasonable SQL. Thank you! – iUknwn Mar 30 '20 at 20:41

Salah Akbari · Answer 2 · 2020-03-28T06:03:28.420

0

Your method couldn't be translated to T-SQL, Linq to Entities couldn't recognize it. You can modify the code as below (adding AsEnumerable after AsNoTracking):

.Include(x => x.Project)
.AsNoTracking()
.AsEnumerable()

With AsEnumerable after data is loaded, any further operation is performed using Linq to Objects, on the data already in memory.

edited Mar 28 '20 at 06:03

answered Mar 28 '20 at 02:30

Salah Akbari

39,330
10
79
109

While this does get the correct result, the call to AsEnumerable will result in the entire Result table being downloaded and placed in memory, which is sub-optimal (there are many results, but few projects, hence why I'm trying to do the join on the server). – iUknwn Mar 28 '20 at 06:18

score 0 · Answer 3 · answered Mar 28 '20 at 06:25

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.

Get Distinct Entries Based On Specific Column in Entity Framework

3 Answers3

Linked