How to Select Top Distinct Values from a LINQ Group by Results using Entity Framework Core

Question: How do I select the top distinct values from a #Linq #GroupBy result set, every time I try to #OrderBy and then select top results an error is thrown. See the error message below:

InvalidOperationException: The LINQ expression 'ProjectionBindingExpression: 0' could not be translated. Either rewrite the query in a form that can be translated, or switch to client evaluation explicitly by inserting a call to 'AsEnumerable', 'AsAsyncEnumerable', 'ToList', or 'ToListAsync'


Below is my code:

 var fromDB = _context.MyEntity.AsNoTracking().OrderByDescending(a => a.Created).GroupBy(x => x.ID, (key, g) => g.OrderBy(e => 
                        e.Created).LastOrDefault()).Take(35)
                        .Select(a => new MyEntity
                         {
                             Created = a.Created,
                             ID = a.ID
                         }) .ToList();

[Expected Results]: I would like to retrieve only two columns of the #Database Table Result and then order that based on the Date Created. I would like the values to be #unique as the ID is not necessary a #Primary Key. How do I do that?



Login to See the Rest of the Answer

Answer: First of all, just like the error says, the #Query could not be translated into functional #SQL Query that #EFCore sends to the database. Below are the solutions:

1. Either follow what the error message says to resolve the issue by appending the ".AsEnumerable" to see if that helps or completely rewrite your Query and pay attention to the Execution Time the EF Core shows in the Console when testing.

2. Write your query as below to get the expected results
 

//Brute force approach
var fromDB = _context.MyEntity.AsNoTracking().GroupBy(t => t.DataID).Select(k => k.First()).ToList().OrderByDescending(a => a.Created).Take(35).ToList();//You see that there are two .ToList(), this is not the most optimal code but it gets the result

Leave a comment below if this helped you. Good luck!!