Is it possible to use reflection with linq to entity?

Question

I'm trying to clean up my code a little by creating an extension method to generically handle filtering.

Here is the code I'm trying to clean.

var queryResult = (from r in dc.Retailers select r);
if (!string.IsNullOrEmpty(firstName))
    queryResult = queryResult.Where(ex => SqlFunctions.PatIndex(firstName.Trim(), ex.FirstName.Trim()) > 0);
if (!string.IsNullOrEmpty(lastName))
    queryResult = queryResult.Where(ex => SqlFunctions.PatIndex(lastName.Trim(), ex.LastName.Trim()) > 0);
if (!string.IsNullOrEmpty(companyName))
    queryResult = queryResult.Where(ex => SqlFunctions.PatIndex(companyName.Trim(), ex.CompanyName.Trim()) > 0);
if (!string.IsNullOrEmpty(phone))
    queryResult = queryResult.Where(ex => SqlFunctions.PatIndex(phone.Trim(), ex.Phone.Trim()) > 0);
if (!string.IsNullOrEmpty(email))
    queryResult = queryResult.Where(ex => SqlFunctions.PatIndex(email.Trim(), ex.Email.Trim()) > 0);
if (!string.IsNullOrEmpty(city))
    queryResult = queryResult.Where(ex => SqlFunctions.PatIndex(city.Trim(), ex.City.Trim()) > 0);
if (!string.IsNullOrEmpty(zip))
    queryResult = queryResult.Where(ex => SqlFunctions.PatIndex(zip.Trim(), ex.Zip.Trim()) > 0);
if (!string.IsNullOrEmpty(country))
    queryResult = queryResult.Where(ex => SqlFunctions.PatIndex(country.Trim(), ex.Country.Trim()) > 0);
if (!string.IsNullOrEmpty(state))
    queryResult = queryResult.Where(ex => SqlFunctions.PatIndex(state.Trim(), ex.State.Trim()) > 0);

It's clearly very repetitious. So I tried to create an extension method that filtered by a property using reflection. Here is the method.

public static void FilterByValue<T>(this IQueryable<T> obj, string propertyName, string propertyValue)
{
    if (!string.IsNullOrEmpty(propertyValue))
    {
        obj =
            obj.Where(
                ex =>
                    SqlFunctions.PatIndex(propertyValue.Trim(), (string)typeof(T).GetProperty(propertyName,
                        BindingFlags.Public | BindingFlags.Instance | BindingFlags.IgnoreCase).GetValue(ex)) > 0
                    );
    }
}

and it's to be called like so:

var queryResult = (from r in dc.Retailers select r);
queryResult.FilterByValue("firstname", firstName);

But, I get an error when the linq executes, stating that "GetValue" isn't recognized in linq to entity.

So, is there any other way to clean this up, or do I have to leave it ugly?

Answer 1

Technically, yes, you could do it, but you'd need to construct the Expression yourself to pass to Where.

That said, rather than accepting the property as a string value you should consider instead accepting an Expression<Func<T, string>> as a parameter so that you have compile time support for verifying that the selected object is valid.

We'll start out with an expression that represents the generic portion; it'll represent a function with*two* parameters, the object and the value of the given property. We can then replace all instances of that second parameter with the property selector that we've defined in the actual method's parameters.

public static IQueryable<T> FilterByValue<T>(
    this IQueryable<T> obj,
    Expression<Func<T, string>> propertySelector,
    string propertyValue)
{
    if (!string.IsNullOrEmpty(propertyValue))
    {
        Expression<Func<T, string, bool>> expression =
            (ex, value) => SqlFunctions.PatIndex(propertyValue.Trim(),
                value.Trim()) > 0;

        var newSelector = propertySelector.Body.Replace(
            propertySelector.Parameters[0],
            expression.Parameters[0]);

        var body = expression.Body.Replace(expression.Parameters[1], 
            newSelector);
        var lambda = Expression.Lambda<Func<T, bool>>(
            body, expression.Parameters[0]);

        return obj.Where(lambda);
    }
    else
        return obj;
}

And this method uses a function to replace all instances of one expression with another in a given expression. The implementation of that is:

public class ReplaceVisitor : ExpressionVisitor
{
    private readonly Expression from, to;
    public ReplaceVisitor(Expression from, Expression to)
    {
        this.from = from;
        this.to = to;
    }
    public override Expression Visit(Expression node)
    {
        return node == from ? to : base.Visit(node);
    }
}

public static Expression Replace(this Expression expression,
    Expression searchEx, Expression replaceEx)
{
    return new ReplaceVisitor(searchEx, replaceEx).Visit(expression);
}

If you really want to accept the property name as a string then just replace the definition of newSelector with the following:

var newSelector = Expression.Property(expression.Parameters[0], propertyName);