GroupBy Expression failed to translate

Question

//Model
public class Application
{
    [Key]
    public int ApplicationId { get; set; }
    public DateTime CreatedAt { get; set; }
    public DateTime ConfirmedDate { get; set; }
    public DateTime IssuedDate { get; set; }
    public int? AddedByUserId { get; set; }
    public virtual User AddedByUser { get; set; }
    public int? UpdatedByUserId { get; set; }
    public virtual User UpdatedByuser { get; set; }
    public string FirstName { get; set; }
    public string MiddleName { get; set; }
    public string LastName { get; set; }
    public string TRN { get; set; }
    public string EmailAddress { get; set; }
    public string Address { get; set; }
    public int ParishId { get; set; }
    public Parish Parish { get; set; }
    public int? BranchIssuedId { get; set; }
    public BranchLocation BranchIssued { get; set; }
    public int? BranchReceivedId { get; set; }
    public BranchLocation BranchReceived {get; set; }
}

public async Task<List<Application>> GetApplicationsByNameAsync(string name)
{
    if (string.IsNullOrEmpty(name))
        return null;
    return await _context.Application
        .AsNoTracking()
        .Include(app => app.BranchIssued)
        .Include(app => app.BranchReceived)
        .Include(app => app.Parish)
        .Where(app => app.LastName.ToLower().Contains(name.ToLower()) || app.FirstName.ToLower()
        .Contains(name.ToLower()))
        .GroupBy(app => new { app.TRN, app })
        .Select(x => x.Key.app)
        .ToListAsync()
        .ConfigureAwait(false);
}

The above GroupBy expression fails to compile in VS Studio. My objective is to run a query filtering results by name containing a user given string and then it should group the results by similar TRN numbers returning a list of those applications to return to the view. I think I am really close but just cant seem to figure out this last bit of the query. Any guidance is appreciated.

Error being presented

InvalidOperationException: The LINQ expression 'DbSet<Application>
.Where(a => a.LastName.ToLower().Contains(__ToLower_0) || a.FirstName.ToLower().Contains(__ToLower_0))
.GroupBy(
source: a => new {
TRN = a.TRN,
app = a
},
keySelector: a => a)' 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 either AsEnumerable(), AsAsyncEnumerable(), ToList(), or ToListAsync()

UPDATE Seems it is definitely due to a change in how .net core 3.x and EF core play together since recent updates. I had to change it to client evaluation by using AsEnumerable() instead of ToListAsync(). The rest of the query given by Steve py works with this method. I was unaware even after reading docs how the groupby really worked in LINQ, so that has helped me a lot. Taking the query to client side eval may have performance issues however.

Answer 1

Based on this:

I want to group by TRN which is a repeating set of numbers eg.12345, in the Application table there may be many records with that same sequence and I only want the very latest row within each set of TRN sequences.

I believe this should satisfy what you are looking for:

return await _context.Application
    .AsNoTracking()
    .Include(app => app.BranchIssued)
    .Include(app => app.BranchReceived)
    .Include(app => app.Parish)
    .Where(app => app.LastName.ToLower().Contains(name.ToLower()) || app.FirstName.ToLower()
    .Contains(name.ToLower()))
    .GroupBy(app => app.TRN)
    .Select(x => x.OrderByDescending(y => y.CreatedAt).First())
    .ToListAsync()
    .ConfigureAwait(false);

The GroupBy expression should represent what you want to group by. In your case, the TRN. From there when we do the select, x represents each "group" which contains the Enumarable set of Applications that fall under each TRN. So we order those by the descending CreatedAt date to select the newest one using First.

Give that a shot. If it's not quite what you're after, consider adding an example set to your question and the desired output vs. what output / error this here produces.