Entity Framework/Linq Get Last Record From List With Condition

Question

I have a list of records, by multiple UserID's.

TableID UserID Status
------------------------
1 3 3 
2 4 3
3 3 4
4 4 3
5 5 4
6 5 3

I'm trying to get all the UserID's where the most recent record for them (TableID) has a status of 4. From the example, it should return just 1 record : TableID 3, UserID 3.

UserID 5 should not be returned, as although they have a Status of 4, the most recent entry for them was Status 3.

I've tried this:-

await _context.Orders!
 .GroupBy(u => u.UserID)
 .Select(g => g.OrderBy(x => x.TableID).Where(x => x.Status == 4).FirstOrDefault())
 .ToListAsync();

But this is returning any UserID that has a Status of 4, even though it's not the most recent record.

• Panagiotis Kanavos: You can't do that by grouping in SQL. Grouping in SQL eliminates rows. In EF it will either do the same or load everything and reshape the results. You'd need a windowing function like LAST_VALUE(Status) OVER (PARTITION BY User ID ORDER BY TableID) to get the last status per user.
• Charlieface: @PanagiotisKanavos A GroupBy followed by Select containing a FirstOrDefault usually results in a ROW_NUMBER style query.
• Panagiotis Kanavos: That's more complicated and more expensive than LAST_VALUE which returns the actual result. Especially if Status is covered by an index. I see the current efforts to introduce Window functions) go through GroupBy+SelectMany though, so I wouldn't be surprised if .SelectMany(........g....Last() was translated to LAST_VALUE at some point. I'd rather use FromSqlRaw even then
• Charlieface: @PanagiotisKanavos I don't think that's true at all, at least in SQL Server. LAST_VALUE uses a worktable under the hood, so is actually less efficient whne you only need the last row. And an index over Status won't help you because the WHERE is after the window function. See dbfiddle.uk/RT3g9AIh note how the index is just scanned and re-sorted.
• Panagiotis Kanavos: If you try with INDEX ix (UserID, TableID, Status) the second query cost is 0.003 while ROW_NUMBER remains 0.014.

Answer 1

You need to put the Where after the Select. The OrderBy also needs to be Descending.

await _context.Orders!
 .GroupBy(u => u.UserID)
 .Select(g => g.OrderByDescending(x => x.TableID).FirstOrDefault())
 .Where(x => x.Status == 4))
 .ToListAsync();

However an ongoing bug in EF Core may cause this type of query to fail.

• Sherry8212: Thanks, I can see how this will work, but I'm now getting the error System.Collections.Generic.KeyNotFoundException: 'The given key 'EmptyProjectionMember' was not present in the dictionary.'
• Charlieface: Yes, that's the bug. You might be able to work around it using some other construct, but maybe only raw SQL is an option.
• Sherry8212: Thanks, I'll rewrite. Marked as answer as it should have worked.