The LINQ expression could not be translated. Eiither rewrite the query in a form that can be translated

From a SQL table, I'm trying to get the last line of each item. I'm passing a list of users (list of objectIds) and want to get the last job of each of them.

Here is the function:

public async Task<List<Job>> GetLastJobs(List<int> objectIds)
{
    using ManagerContext context = new ManagerContext(_callContext);

    List<Job> jobs = context.Jobs
                            .Where(j => j.ObjectId.HasValue 
                                        && objectIds.Contains(j.ObjectId.Value))
                            .GroupBy(j => j.ObjectId)
                            .Select(j => j.OrderByDescending(p => p.Id)
                            .FirstOrDefault())
                            .ToList();
    return null;
}

At execution time, it returns:

the LINQ expression '(GroupByShaperExpression:
KeySelector: (j.ObjectId),
ElementSelector:(EntityShaperExpression: 
    EntityType: Job
    ValueBufferExpression: 
        (ProjectionBindingExpression: EmptyProjectionMember)
    IsNullable: False
)
)
    .OrderByDescending(p => p.Id)' 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().
See https://go.microsoft.com/fwlink/?linkid=2101038
for more information.

I have no idea how, where to start to solve the problem

Solution

The basic problem is that SQL has no powerful grouping operator like LINQ's GroupBy. SQL GROUP BY must aggregate all non-grouping columns, and there's no FIRST() aggregate function in most RDBMSs. So you have to write this query with Windowing Functions, which EF Core hasn't gotten around to.

The alternative way to write this query can be translated.

  var jobs = db.Jobs.Where(j => j.ObjectId.HasValue && objectIds.Contains(j.ObjectId.Value))
                    .Where(j => j.Id == db.Jobs.Where(j2 => j2.ObjectId == j.ObjectId).Max(j => j.Id))
                    .ToList();

Which translates to

SELECT [j].[Id], [j].[ObjectId]
FROM [Jobs] AS [j]
WHERE ([j].[ObjectId] IS NOT NULL AND [j].[ObjectId] IN (1, 2, 3)) AND ([j].[Id] = (
    SELECT MAX([j0].[Id])
    FROM [Jobs] AS [j0]
    WHERE [j0].[ObjectId] = [j].[ObjectId]))