C#: Search a list of defined queryable fields, different per model, with linq through generics (using entity framework)

Question

*****Scroll down for final working solution*****

All of my Entity Framework models use partials, which implement my own IEntity interface:

public interface IEntity
{
        int Status { get; set; }
        int ID { get; set; }
}

This allows me to filter any Entity which implements this interface, based on the following function (simplified version):

    public static IQueryable<T> FilterByStatus<T>(this IQueryable<T> query, int status) where T : class, IEntity
    {
        return query.Where(m => m.Status == status);
    }

Now I want a function which names all of the properties, which I might want to perform a text query on. Let's say that implementation Foo of IEntity has 2 values (Bar and Baz) that I want to perform queries on.

I currently have:

    public static IQueryable<Foo> FooSearch(this Entities context, string query)
    {
        IQueryable<Foo> result = context.Foo;

        if (!String.IsNullOrEmpty(query))
        {
            result = result.Where(m =>
            m.Bar.ToLower().IndexOf(query.ToLower()) >= 0 ||
            m.Baz.ToLower().IndexOf(query.ToLower()) >= 0);
        }

        return result;
    }

But I want to set it up in a more generic way. Something like:

   public interface IEntity
    {
            int Status { get; set; }
            int ID { get; set; }

            string[] QueryableProperties { get; set; }
    }

And some kind of implementation like (pseudocode):

    public static IQueryable<T> GenericSearch(this IQueryable<T> query, string query) where T : class, IEntity
    {

        if (!String.IsNullOrEmpty(query))
        {
            query = query.Where(m =>
            m[QueryableProperties[0]].ToLower().IndexOf(query.ToLower()) >= 0 ||
                m[QueryableProperties[1]].ToLower().IndexOf(query.ToLower()) >= 0 ||
                // .... //
                m[QueryableProperties[QueryableProperties.Count - 1]].ToLower().IndexOf(query.ToLower()) >= 0)
        }
        return query;
    }

How can I achieve this?

******Working Solution******

Search function:

public static class SearchFilter
{
    private static Expression GetNestedPropertyExpression(Expression expression, string propertyName)
    {
        Expression body = expression;
        foreach (var member in propertyName.Split('.'))
        {
            body = Expression.PropertyOrField(body, member);
        }
        return body;
    }

    private static Expression<Func<T, bool>> GetSearchExpression<T>(string[] propertyNames, string query)
    {
        var parameterExp = Expression.Parameter(typeof(T), "category");
        MethodInfo containsMethod = typeof(string).GetMethod("Contains", new[] { typeof(string) });
        MethodInfo toLowerMethod = typeof(string).GetMethod("ToLower", Type.EmptyTypes);
        List<Expression> methodCalls = new List<Expression>();
        foreach (string propertyName in propertyNames)
        {
            var propertyExp = GetNestedPropertyExpression(parameterExp, propertyName);
            var queryValue = Expression.Constant(query.ToLower(), typeof(string));
            var toLowerMethodExp = Expression.Call(propertyExp, toLowerMethod);
            var containsMethodExp = Expression.Call(toLowerMethodExp, containsMethod, queryValue);
            methodCalls.Add(containsMethodExp);
        }
        var orExp = methodCalls.Aggregate((left, right) => Expression.Or(left, right));

        return Expression.Lambda<Func<T, bool>>(orExp, parameterExp);
    }

    public static IQueryable<T> Search<T>(this IQueryable<T> query, string property) where T : class, IEntity
    {
        var filterAttributes = typeof(T).GetCustomAttributes(
        typeof(FilterableAttribute), true
        ).FirstOrDefault() as FilterableAttribute;

        if (filterAttributes == null) {
            return query;
        }

        var filterableColumns = filterAttributes.FilterableAttributes;
        if (filterableColumns == null || filterableColumns.Count() == 0)
        {
            return query;
        }

        if (property == null)
        {
            return query;
        }

        return query.Where(GetSearchExpression<T>(filterableColumns, property));
    }
}

Decorator (example: both a property of my model, and a nested property):

   [Filterable(FilterableAttributes = new string[] {
        nameof(Foo),
        nameof(Bar) + "." + nameof(Models.MyConnectedModel.Baz)
    })]
    public partial class MyConnectedModel: IEntity
    {

    }

Answer 1

Nice question :) Here's how you can do this:

static Expression<Func<T, bool>> GetExpression<T>(string[] propertyNames, string query)
{
  var parameterExp = Expression.Parameter(typeof(T), "category");
  MethodInfo containsMethod = typeof(string).GetMethod("Contains", new[] { typeof(string) });
  MethodInfo toLowerMethod = typeof(string).GetMethod("ToLower",Type.EmptyTypes);
  List<Expression> methodCalls = new List<Expression>();
  foreach (string propertyName in propertyNames)
  {
    var propertyExp = Expression.Property(parameterExp, propertyName);
    var queryValue = Expression.Constant(query.ToLower(), typeof(string));
    var toLowerMethodExp = Expression.Call(propertyExp, toLowerMethod);
    var containsMethodExp = Expression.Call(toLowerMethodExp, containsMethod, queryValue);
    methodCalls.Add(containsMethodExp);
  }
  var orExp = methodCalls.Aggregate((left, right) => Expression.Or(left, right));

  return Expression.Lambda<Func<T, bool>>(orExp, parameterExp);
}

And then you can use it like this (query is an IQueryable<MyEntity>)

query=query.Where(GetExpression<MyEntity>(queryableProperties,"SomeValue"));

Answer 2

IMO, it can be surprisingly easy:

IQueryable<T> GenericSearch<T>(this IQueryable<T> items, string query)
{
    var queryableProperties = items
        .First()
        .GetType()
        .GetProperties()
        .Where(p => p.PropertyType == typeof(string))
        .ToList();

    return items.Where(i => queryableProperties.Any(p => ((string)p.GetValue(i)).Contains(query)));
}

This searches all properties of type string in a list of items.

It can be a class method, an extension method, a static function, it's easy to understand and works well. It can be extended to Fields or restricted to some interfaces easily.

Adjust to your liking.