How to rewrite the where clause so that the exception is not thrown EF Core

Question

I want to filter a dbset using a where clause but both predicates dont work when using AsQueryable and the exception:

The linq expression could not be translated

is thrown

...
var objects = myObject; //Ienumerable

return await dbset.AsQueryable()
.Where(entity => objects.Any(obj => obj.uId == entity.uId &&  obj.cId == entity.cId))
.ToArrayAsync(cancellationToken)
.ConfigureAwait(false); // not working

return await dbset.AsQueryable().
Where(entity => objects.Contains(new Object(entity.cId, entity.uId))
.ToArrayAsync(cancellationToken)
.ConfigureAwait(false); // also not working

When I change AsQueryable to AsEnumerable it is working but since the set can be quite large I want to stick with AsQueryable. How could I rewrite the where clause so that the exception is not thrown?

The issue is not `AsQueryable()`. You can (and should) remove it. The issue is that `objects.Any` isn't translatable, which `AsEnumerable` conceals in a very expensive manner. — Gert Arnold, Aug 15 '23 at 12:59
@GertArnold What would you suggest to use instead of AsQueryable? — G.Don, Aug 15 '23 at 13:01
@GertArnold Could you explain or link a website that explains why one should not use AsQueryable? — G.Don, Aug 15 '23 at 13:09

score 0 · Accepted Answer · answered Aug 15 '23 at 12:03

EF Core query provider is unable to translate certain operations from your LINQ expression to SQL when using AsQueryable. The methods Any and Contains in your current queries are causing this issue. You must rewrite your query to make it more compatible EF Core's query translation capabilities.

var objectIds = objects.Select(object => new { object.uId, object.cId }).ToList();

return await dbset.AsQueryable()
    .Join(objectIds,
        entity => new { entity.uId, entity.cId },
        objectId => objectId,
        (entity, objectId) => entity)
    .ToArrayAsync(cancellationToken)
    .ConfigureAwait(false);

@SvyatoslavDanyliv Really? Since when joins to inmemory collections are supported? — Ivan Stoev, Aug 15 '23 at 13:24

How to rewrite the where clause so that the exception is not thrown EF Core

1 Answers1