Expressions with LinqKit could not be translated - EF Core

Question

I am really wondering if anyone can help me out. We have a very generic approach to build our Expressions with LinqKit. With internal Lists everything is working fine, but when using EF Core we got some Errors.

System.InvalidOperationException: The LINQ expression 'DbSet<Entity>()
    .Where(k => False || __propertyInfo_0.GetValue(k) != null && __propertyInfo_0.GetValue(k).ToString().Contains(__GenericTableOptions_BaseQueryParameters_Search_1) && True)' 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'. See https://go.microsoft.com/fwlink/?linkid=2101038 for more information.

The Generic Expression Builder looks like this

 private Expression<Func<TEntity, bool>> BuildGenericSearchExpression()
    {
        Expression<Func<TEntity, bool>> expression = x => false;

        var searchFields = GenericTableOptions.SearchColumns;
        var propertyInfos = GetColumnsAsProperties(typeof(TEntity), searchFields).ToList();

        if (propertyInfos.Count == 0)
        {
            // Returning true to allow AND chaining of multiple conditions
            return x => true;
        }

        foreach (var propertyInfo in propertyInfos)
        {
            expression = expression.Or(x =>
                propertyInfo.GetValue(x) != null &&
                propertyInfo.GetValue(x)!.ToString()!.Contains(GenericTableOptions.BaseQueryParameters.Search));
        }

        return expression;
    }

Anyone has an idea to sort this out?

Answer 1

You cannot use expression.Or(...) here, you have to build LamdaExpression dynamically.

Below is corrected code, also removed Null check, it is not needed for SQL translation.

private Expression<Func<TEntity, bool>> BuildGenericSearchExpression()
{
    var searchFields = GenericTableOptions.SearchColumns;
    var propertyInfos = GetColumnsAsProperties(typeof(TEntity), searchFields).ToList();

    if (propertyInfos.Count == 0)
    {
        // Returning true to allow AND chaining of multiple conditions
        return x => true;
    }

    Expression? predicate = null;
    var param = Expression.Parameter(typeof(TEntity), "e");
    var searchExpr = Expression.Constant(GenericTableOptions.BaseQueryParameters.Search);

    foreach (var propertyInfo in propertyInfos)
    {
        var memberAccess = EnsureString(Expression.MakeMemberAccess(param, propertyInfo));
        var condition = Expression.Call(memberAccess, nameof(string.Contains), Type.EmptyTypes, searchExpr);

        predicate = predicate == null ? condition : Expression.OrElse(predicate, condition);
    }

    var predicateLambda = Expression.Lambda<Func<TEntity, bool>>(predicate!, param);

    return predicateLambda;
}

// Helper method

private static Expression EnsureString(Expression expression)
{
    if (expression.Type == typeof(string))
        return expression;

    if (expression.Type != typeof(object))
        expression = Expression.Convert(expression, typeof(object));

    expression = Expression.Call(_toStringMethod, expression);

    return expression;
}

private static MethodInfo _toStringMethod = typeof(Convert).GetMethods()
    .Single(m =>
        m.Name == nameof(Convert.ToString) && m.GetParameters().Length == 1 &&
        m.GetParameters()[0].ParameterType == typeof(object)
    );