Search through Where clause in LINQ, using dynamic properties

Question

I'm basically trying to construct a query, and I don't know why microsoft made this so difficult in Entity Framework and LINQ. I have various parameter STRINGS. So if you see a variable, assume it's a string passed in from somewhere.

             users = this.entities.tableUsers
                .Where(searchfield+" LIKE %@0%", search)
                .OrderBy(x => x.GetType().GetProperty(order_by).GetValue(x, null).ToString())
                .Skip(Convert.ToInt32(limit_begin))
                .Take(Convert.ToInt32(limit_end))
                .ToList();

My question is what to put inside "Where()" function in LINQ.

I want to search a field with string "searchfield", for the value .contains() "search".

Not sure why Visual Studio won't let me do this easily.

I've tried this as well, no luck:

.Where(x => x.GetType().GetProperty(searchfield).GetValue(x, null).ToList().Contains(search))

Note: I don't want to install any new libraries, this should be incredibly easy and simple for a modern language. I don't mind if the query returns all the rows and I search through it AFTER with .Contains().

Answer 1

This is not trivial, but I believe it can be done. The following has not been tested. The code is borrowed from here.

Create a helper method somewhere like

public static Expression<Func<T, bool>> GetContainsExpression<T>(string propertyName, string containsValue)
{
    var parameterExp = Expression.Parameter(typeof(T), "type");
    var propertyExp = Expression.Property(parameterExp, propertyName);
    MethodInfo method = typeof(string).GetMethod("Contains", new[] { typeof(string) });
    var someValue = Expression.Constant(propertyValue, typeof(string));
    var containsMethodExp = Expression.Call(propertyExp, method, someValue);

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

public static Expression<Func<T, TKey>> GetPropertyExpression<T, TKey>(string propertyName)
{
    var parameterExp = Expression.Parameter(typeof(T), "type");
    var exp = Expression.Property(parameterExp, propertyName);
    return Expression.Lambda<Func<T, TKey>>(exp, parameterExp);
}

Use it like

users = this.entities.tableUsers
                     .Where(GetContainsExpression<User>(searchfield, search))
                     .OrderBy(GetPropertyExpression<User, string>(searchfield))
                     ...

UPDATE

As an alternative, you could create extension methods to provide a cleaner syntax. Create the following methods in a static class somewhere:

    public static IQueryable<T> WhereStringContains<T>(this IQueryable<T> query, string propertyName, string contains)
    {
        var parameter = Expression.Parameter(typeof(T), "type");
        var propertyExpression = Expression.Property(parameter, propertyName);
        MethodInfo method = typeof(string).GetMethod("Contains", new[] { typeof(string) });
        var someValue = Expression.Constant(contains, typeof(string));
        var containsExpression = Expression.Call(propertyExpression, method, someValue);

        return query.Where(Expression.Lambda<Func<T, bool>>(containsExpression, parameter));
    }

    public static IOrderedQueryable<T> OrderBy<T>(this IQueryable<T> query, string propertyName)
    {
        var propertyType = typeof(T).GetProperty(propertyName).PropertyType;
        var parameter = Expression.Parameter(typeof(T), "type");
        var propertyExpression = Expression.Property(parameter, propertyName);
        var lambda = Expression.Lambda(propertyExpression, new[] { parameter });

        return typeof(Queryable).GetMethods()
                                .Where(m => m.Name == "OrderBy" && m.GetParameters().Length == 2)
                                .Single()
                                .MakeGenericMethod(new[] { typeof(T), propertyType })
                                .Invoke(null, new object[] { query, lambda }) as IOrderedQueryable<T>;
    }

    public static IOrderedQueryable<T> OrderByDescending<T>(this IQueryable<T> query, string propertyName)
    {
        var propertyType = typeof(T).GetProperty(propertyName).PropertyType;
        var parameter = Expression.Parameter(typeof(T), "type");
        var propertyExpression = Expression.Property(parameter, propertyName);
        var lambda = Expression.Lambda(propertyExpression, new[] { parameter });

        return typeof(Queryable).GetMethods()
                                .Where(m => m.Name == "OrderByDescending" && m.GetParameters().Length == 2)
                                .Single()
                                .MakeGenericMethod(new[] { typeof(T), propertyType })
                                .Invoke(null, new object[] { query, lambda }) as IOrderedQueryable<T>;
    }

Then you can call them like:

var users = this.entities.tableUsers.WhereStringContains(searchField, search)
                                    .OrderBy(searchField);

Answer 2

this should be incredibly easy and simple for a modern language

No, it should not if it goes against that language paradigm. LINQ and Entity Framework (as well as any other decent ORM out there) are made precisely to avoid what you're trying to accomplish: non-typed and non-compiler-verifiable queries. So basically you're forcing square peg into round hole.

You can still take a look at Dynamic LINQ.

Answer 3

You'll have to build an expression tree to pass to the Where method. Here's a loose adaptation of some code I have lying about:

string searchfield, value; // Your inputs
var param = Expression.Parameter(typeof(User), "user");

return Expression.Lambda<Func<T, bool>>(
    Expression.Call(
        Expression.Property(
            param,
            typeof(User).GetProperty(searchfield)),
        typeof(string).GetMethod("Contains"),
        Expression.Constant(value)),
    param);

That will generate an appropriate expression to use as the parameter to Where.

EDIT: FYI, the resultant expression will look something like user => user.Foo.Contains(bar).

EDIT: To sort, something like this (ripped from my DynamicOrderList class):

private IQueryable<T> OrderQuery<T>(IQueryable<T> query, OrderParameter orderBy)
{
    string orderMethodName = orderBy.Direction == SortDirection.Ascending ? "OrderBy" : "OrderByDescending";
    Type t = typeof(T);

    var param = Expression.Parameter(t, "user");
    var property = t.GetProperty(orderBy.Attribute);

    return query.Provider.CreateQuery<T>(
        Expression.Call(
            typeof(Queryable),
            orderMethodName,
            new Type[] { t, typeof(string) },
            query.Expression,
            Expression.Quote(
                Expression.Lambda(
                    Expression.Property(param, property),
                    param))
        ));
}

Answer 4

My answer to your other question about this:

When creating dynamic linq sorting and searching order statements in Entity Framework