Forcing client side evaluation on a predicate builder expression in .net core 3.1

Question

I am using LinqKit's predicate builder expressions for search functions. I build a dynamic expression which is then applied on the entity.

Had this working before, but after migrating to .net core 3.1, I am getting errors due to the restriction on client side valuations.

I understand this can be overcome by forcing AsEnumerable() casting.

However I don't understand exactly how to do this on predicate expressions.

Predicate builder function:

private Expression<Func<A, bool>> BuildDynamicWhereClauseForA(string searchValue)
{
    var predicate = PredicateBuilder.New<A>(true);

    if (!string.IsNullOrWhiteSpace(searchValue))
    {
        var searchTerms = searchValue.Split(' ').ToList().ConvertAll(x => x.ToLower());

        foreach (var item in searchTerms)
        {
            predicate = predicate.Or(x => x.col1.ToLower().Contains(item));
            predicate = predicate.Or(x => x.col2.ToLower().Contains(item));
            predicate = predicate.Or(x => x.col3.ToString().ToLower().Contains(item));
        }
    }

    return predicate;
}

Function which performs actual database call:

private List<AD> GetDataFromDBForA(string searchBy ...)
{
    var whereClause = BuildDynamicWhereClauseForA(searchBy);

    List<AD> result;

    result =  db.A.AsExpandable()
                  .Where(whereClause)
                  .OrderBy(sortBy)
                  .Select(m => new AD
                                   {
                                      ...
                                      ...
                                   })
                  .Skip(skip)
                  .Take(take)
                  .ToList()

    return result;
}

This is the error I get:

The LINQ expression 'DbSet .Where(e => e.col1.ToLower().Contains(__item_0) || e.col2.ToLower().Contains(__item_0) || e.Col3.ToString().ToLower().Contains(__item_0))' 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().

I understand why this is happening.

But I don't understand predicates as well and can't seem to cast the result to enumerable or list to force client side evaluation.

What I have tried:

 result =  db.A.AsEnumerable()
               .AsQueryable()
               .Where(whereClause)
               .OrderBy(sortBy)

Also you definitely should not do `db.A.AsEnumerable()` cause it will fetch whole table from database to memory. — Guru Stron, May 08 '20 at 10:14
@GuruStron I am stuck on how to force client side evaluation. My only lead is Microsoft docs which says use ```AsEnumerable or ToList``` before set property, either gives me an error saying ```IEnumerable or .ToList() does not contain a definition for where``` how I am trying to force client side evaluation is through variations of the following : ```db.A .AsExpandable() .OrderBy(sortBy) .ToList() .Where(whereClause)``` — Abhilash Gopalakrishna, May 08 '20 at 10:28
variations include db.A.AsEnumerable().where(whereclause) and db.A.AsEnumerable().AsQueryable().where(whereclause) — Abhilash Gopalakrishna, May 08 '20 at 10:30
What error do you get in the example which you provided in question(`.AsEnumerable().AsQueryable().Where(whereClause)`)? — Guru Stron, May 08 '20 at 10:37
@GuruStron I get the same run time error => ```The LINQ expression 'DbSet .Where(e => e.col1.ToLower().Contains(__item_0) || e.col2.ToLower().Contains(__item_0) || e.Col3.ToString().ToLower().Contains(__item_0))' 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().``` — Abhilash Gopalakrishna, May 08 '20 at 10:41
Have you tried to update `LinqKit` to [3.0.0](https://www.nuget.org/packages/LinqKit.Microsoft.EntityFrameworkCore/3.0.0) version? — Guru Stron, May 08 '20 at 10:45

Forcing client side evaluation on a predicate builder expression in .net core 3.1

0 Answers0