CODEWITHSUNDEEP
entity-frameworklinq
CloudAnywhere
CloudAnywhere

Reputation: 759

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 passign a list of users (list of objectIds) and want to get the last job of each of them. Here is the function below.

   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 exexcution 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

Upvotes: 5

Views: 3891

Answers (1)

David Browne - Microsoft
David Browne - Microsoft

Reputation: 89419

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]))

Upvotes: 7

Related Questions