System.InvalidOperationException: 'The LINQ expression could not be translated. Either rewrite the query in a form that can be translated

user_5179734 0
Viewed 145

I am trying to get all pens when are not blue by using below expression. there are two lists pens and bluepens...

var pensToDelete = _repository.GetPens().Where(x => bluePens.All(y => y.Id != x.Id));

when I am accessing pensToDelete as below

if (pensToDelete .Count() > 0)
{
}

I get the below error:

System.InvalidOperationException: 'The LINQ expression 'DbSet() .Where(a => __bluePens_0 .All(y => y.Id != a.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 'AsEnumerable', 'AsAsyncEnumerable', 'ToList', or 'ToListAsync'.

Not sure how to fix this

entity-framework linq c#
2 Answers

In order to use an expression in LINQ it must be translatable to the underlying provider when you get InvalidOperationException it may because of failed translating the LINQ expression to equivalent SQL Query.

As we discussed in comments be careful when you want using ToList(), always try to use it after your main query because it is costly and retrieve all the records before anything something like Where().Tolist() not ToList().Where()

How about this:

var pensToDelete = _repository.GetPens()
                              .Where(x => !bluePens.Select(s => s.Id).Contains(x.Id));
user_18626580

The issue can be resolved rather quickly, as mentioned in the error message by using enumerating the results by using e.g.: ToList.

The downside of this is that it will be processed in memory and not in your SQL server.

If that is acceptable, this is how to do it;

var pensToDelete = _repository.GetPens().ToList()
                              .Where(x => bluePens.All(y => y.Id != x.Id));
user_24169580
Related