How to make all fields search in ASP.Net Api

Question

I have this Get method:

[HttpGet]
public async Task<ActionResult<IEnumerable<Customer>>> GetCustomers(string s, int page, int page_size)
{
    IQueryable<Customer> query = _context.Customers;
    if (!string.IsNullOrEmpty(s))
    {
        var stringProperties = typeof(Customer).GetProperties();
        query = query.Where(c => stringProperties.Any(prop => prop.GetValue(c, null).ToString().Contains(s)));
    }
    return await query.Skip((page - 1) * page_size).Take(page_size).ToListAsync();
}

But my realization of search doesn't work, I get this error

.Where(m => __stringProperties_0 .Any(prop => prop.GetValue(obj: m, index: null).ToString().Contains(__8__locals1_s_1))) could not be translated

How can I fix this?

Answer 1

Well, basic mistake that you are dealing with PropertyInfo.GetValue. On the SQL server side you do not have objects and cannot get values in that way. What you ca do it is instruct EF how compare properties.

I have created universal function, which may help to create such queries. Instead of just dealing with strings it can be adopted to any type.

Also consider to use extensions, if it is possible.

public static IQueryable<Customer> FilterCustomers(this IQueryable<Customer> query, string s)
{
    if (!string.IsNullOrEmpty(s))
    {
        query = query.FilterByProperties(s, (prop, value) => prop.Contains(value), true)
    }
    return query;
}

public static Task<List<T>> PaginateAsync<T>(this IQueryable<T> query, int page, int page_size)
{
    return query.Skip((page - 1) * page_size).Take(page_size).ToListAsync();
}

[HttpGet]
public Task<List<Customer>> GetCustomers(string s, int page, int page_size)
{
    var query = _context.Customers.FilterCustomers(s);
    return query.PaginateAsync(page, page_size);
}

And implementation:

public static class QueryableExtensions
{
    public static Expression<Func<T, bool>> MakePropertiesPredicate<T, TValue>(Expression<Func<TValue, TValue, bool>> pattern, TValue searchValue, bool isOr)
    {
        var parameter = Expression.Parameter(typeof(T), "e");
        var searchExpr = Expression.Constant(searchValue);

        var predicateBody = typeof(T).GetProperties()
            .Where(p => p.PropertyType == typeof(TValue))
            .Select(p =>
                ExpressionReplacer.GetBody(pattern, Expression.MakeMemberAccess(
                    parameter, p), searchExpr))
            .Aggregate(isOr ? Expression.OrElse : Expression.AndAlso);

        return Expression.Lambda<Func<T, bool>>(predicateBody, parameter);
    }

    public static IQueryable<T> FilterByProperties<T, TValue>(this IQueryable<T> query, TValue searchValue,
        Expression<Func<TValue, TValue, bool>> pattern, bool isOr)
    {
        return query.Where(MakePropertiesPredicate<T, TValue>(pattern, searchValue, isOr));
    }

    class ExpressionReplacer : ExpressionVisitor
    {
        readonly IDictionary<Expression, Expression> _replaceMap;

        public ExpressionReplacer(IDictionary<Expression, Expression> replaceMap)
        {
            _replaceMap = replaceMap ?? throw new ArgumentNullException(nameof(replaceMap));
        }

        public override Expression Visit(Expression node)
        {
            if (node != null && _replaceMap.TryGetValue(node, out var replacement))
                return replacement;
            return base.Visit(node);
        }

        public static Expression Replace(Expression expr, Expression toReplace, Expression toExpr)
        {
            return new ExpressionReplacer(new Dictionary<Expression, Expression> { { toReplace, toExpr } }).Visit(expr);
        }

        public static Expression Replace(Expression expr, IDictionary<Expression, Expression> replaceMap)
        {
            return new ExpressionReplacer(replaceMap).Visit(expr);
        }

        public static Expression GetBody(LambdaExpression lambda, params Expression[] toReplace)
        {
            if (lambda.Parameters.Count != toReplace.Length)
                throw new InvalidOperationException();

            return new ExpressionReplacer(Enumerable.Range(0, lambda.Parameters.Count)
                .ToDictionary(i => (Expression)lambda.Parameters[i], i => toReplace[i])).Visit(lambda.Body);
        }
    }
}