Applying filters to a table

Question

I've written a lengthy procedure that I call a few times to apply filters defined by the customer to a table, as I didn't see how I could turn my column name string into a LINQ column and how I could turn my action string into an action on a string in such way that the function can be used in the LINQ query like:

private void applyFilterAction(ref IQueryable<TempArticle> products, FilterAction action)
{
    products = from p in products where MAGIC(p, action.Column).ToLower().MAGIC2(action.Action, action.Value) == action.AddOrKill select p;
}

I wrote this instead as a temporary solution:

private void applyFilterAction(ref IQueryable<TempArticle> products, FilterAction action)
{
    var actionValue = action.Value.ToLower();
    var column = action.Column;

    if (action.Action.Equals("StartsWith"))
    {
        if (column.Equals("Description"))           products = from p in products where p.Description.ToLower().StartsWith(actionValue) == action.AddOrKill select p;
        else if (column.Equals("LongDescription"))  products = from p in products where p.LongDescription.ToLower().StartsWith(actionValue) == action.AddOrKill select p;
        else if (column.Equals("Provider"))         products = from p in products where p.Provider.ToLower().StartsWith(actionValue) == action.AddOrKill select p;
        else if (column.Equals("ProviderCode"))     products = from p in products where p.ProviderCode.ToLower().StartsWith(actionValue) == action.AddOrKill select p;
        else if (column.Equals("Publisher"))        products = from p in products where p.Publisher.ToLower().StartsWith(actionValue) == action.AddOrKill select p;
        else if (column.Equals("PublisherCode"))    products = from p in products where p.PublisherCode.ToLower().StartsWith(actionValue) == action.AddOrKill select p;
        else if (column.Equals("Custom1"))          products = from p in products where p.Custom1.ToLower().StartsWith(actionValue) == action.AddOrKill select p;
        else if (column.Equals("Custom2"))          products = from p in products where p.Custom2.ToLower().StartsWith(actionValue) == action.AddOrKill select p;
        else if (column.Equals("Custom3"))          products = from p in products where p.Custom3.ToLower().StartsWith(actionValue) == action.AddOrKill select p;
        else if (column.Equals("EanCode"))          products = from p in products where p.EanCode.ToLower().StartsWith(actionValue) == action.AddOrKill select p;
    }

    else if (action.Action.Equals("EndsWith"))
    {
        if (column.Equals("Description"))           products = from p in products where p.Description.ToLower().EndsWith(actionValue) == action.AddOrKill select p;
        else if (column.Equals("LongDescription"))  products = from p in products where p.LongDescription.ToLower().EndsWith(actionValue) == action.AddOrKill select p;
        else if (column.Equals("Provider"))         products = from p in products where p.Provider.ToLower().EndsWith(actionValue) == action.AddOrKill select p;
        else if (column.Equals("ProviderCode"))     products = from p in products where p.ProviderCode.ToLower().EndsWith(actionValue) == action.AddOrKill select p;
        else if (column.Equals("Publisher"))        products = from p in products where p.Publisher.ToLower().EndsWith(actionValue) == action.AddOrKill select p;
        else if (column.Equals("PublisherCode"))    products = from p in products where p.PublisherCode.ToLower().EndsWith(actionValue) == action.AddOrKill select p;
        else if (column.Equals("Custom1"))          products = from p in products where p.Custom1.ToLower().EndsWith(actionValue) == action.AddOrKill select p;
        else if (column.Equals("Custom2"))          products = from p in products where p.Custom2.ToLower().EndsWith(actionValue) == action.AddOrKill select p;
        else if (column.Equals("Custom3"))          products = from p in products where p.Custom3.ToLower().EndsWith(actionValue) == action.AddOrKill select p;
        else if (column.Equals("EanCode"))          products = from p in products where p.EanCode.ToLower().EndsWith(actionValue) == action.AddOrKill select p;
    }

    else if (action.Action.Equals("Contains"))
    {
        if (column.Equals("Description"))           products = from p in products where p.Description.ToLower().Contains(actionValue) == action.AddOrKill select p;
        else if (column.Equals("LongDescription"))  products = from p in products where p.LongDescription.ToLower().Contains(actionValue) == action.AddOrKill select p;
        else if (column.Equals("Provider"))         products = from p in products where p.Provider.ToLower().Contains(actionValue) == action.AddOrKill select p;
        else if (column.Equals("ProviderCode"))     products = from p in products where p.ProviderCode.ToLower().Contains(actionValue) == action.AddOrKill select p;
        else if (column.Equals("Publisher"))        products = from p in products where p.Publisher.ToLower().Contains(actionValue) == action.AddOrKill select p;
        else if (column.Equals("PublisherCode"))    products = from p in products where p.PublisherCode.ToLower().Contains(actionValue) == action.AddOrKill select p;
        else if (column.Equals("Custom1"))          products = from p in products where p.Custom1.ToLower().Contains(actionValue) == action.AddOrKill select p;
        else if (column.Equals("Custom2"))          products = from p in products where p.Custom2.ToLower().Contains(actionValue) == action.AddOrKill select p;
        else if (column.Equals("Custom3"))          products = from p in products where p.Custom3.ToLower().Contains(actionValue) == action.AddOrKill select p;
        else if (column.Equals("EanCode"))          products = from p in products where p.EanCode.ToLower().Contains(actionValue) == action.AddOrKill select p;
    }

    else if (action.Action.Equals("Exact"))
    {
        if (column.Equals("Description"))           products = from p in products where p.Description.ToLower().Equals(actionValue) == action.AddOrKill select p;
        else if (column.Equals("LongDescription"))  products = from p in products where p.LongDescription.ToLower().Equals(actionValue) == action.AddOrKill select p;
        else if (column.Equals("Provider"))         products = from p in products where p.Provider.ToLower().Equals(actionValue) == action.AddOrKill select p;
        else if (column.Equals("ProviderCode"))     products = from p in products where p.ProviderCode.ToLower().Equals(actionValue) == action.AddOrKill select p;
        else if (column.Equals("Publisher"))        products = from p in products where p.Publisher.ToLower().Equals(actionValue) == action.AddOrKill select p;
        else if (column.Equals("PublisherCode"))    products = from p in products where p.PublisherCode.ToLower().Equals(actionValue) == action.AddOrKill select p;
        else if (column.Equals("Custom1"))          products = from p in products where p.Custom1.ToLower().Equals(actionValue) == action.AddOrKill select p;
        else if (column.Equals("Custom2"))          products = from p in products where p.Custom2.ToLower().Equals(actionValue) == action.AddOrKill select p;
        else if (column.Equals("Custom3"))          products = from p in products where p.Custom3.ToLower().Equals(actionValue) == action.AddOrKill select p;
        else if (column.Equals("EanCode"))          products = from p in products where p.EanCode.ToLower().Equals(actionValue) == action.AddOrKill select p;
    }
}

It filters products based on an action object that contains:

• column: Column of the product table that needs to be filtered.
• action: The action to perform on the strings coming from that column.
• value: The value to compare against using the action.

Could I write this in a better way? I didn't get the switch statements in the MAGIC functions to LINQify.

Answer 1

1. YOU HAVE A LOT OF CODE REPEATING ITSELF - it definitely means something. Basically you have two fields to consider - action and column. Since all your columns are considered to be strings so you can separate a) logic which determines which column to take and b) logic which determines how to filter columns. This will lead to dramatical changes in your code.

   Here is a sample:

   private void applyFilterAction(ref IQueryable<TempArticle> products, FilterAction action)
   {
       var actionValue = action.Value.ToLower();
       var column = action.Column;
   
       Func<TempArticle, string> valueExtractor;
       // determining field
       if (column.Equals("Description")) valueExtractor = p => p.Description;
       else if (column.Equals("LongDescription")) valueExtractor = p => p.LongDescription;
       else if (column.Equals("Provider")) valueExtractor = p => p.Provider;
       else if (column.Equals("ProviderCode")) valueExtractor = p => p.ProviderCode;
       else if (column.Equals("Publisher")) valueExtractor = p => p.Publisher;
       else if (column.Equals("PublisherCode")) valueExtractor = p => p.PublisherCode;
       else if (column.Equals("Custom1")) valueExtractor = p => p.Custom1;
       else if (column.Equals("Custom2")) valueExtractor = p => p.Custom2;
       else if (column.Equals("Custom3")) valueExtractor = p => p.Custom3;
       else if (column.Equals("EanCode")) valueExtractor = p => p.EanCode;
       else throw new NotSupportedException(column);
   
       Predicate<string> filteringPredicate;
   
       // determining predicate
       if (action.Action.Equals("StartsWith")) filteringPredicate = s => s.StartsWith(actionValue);
       else if (action.Action.Equals("EndsWith")) filteringPredicate = s => s.EndsWith(actionValue);
       else if (action.Action.Equals("Contains")) filteringPredicate = s => s.Contains(actionValue);
       else if (action.Action.Equals("Exact")) filteringPredicate = s => s.Equals(actionValue);
       else throw new NotSupportedException(action.Action);
   
       products = from p in products where filteringPredicate(valueExtractor(p).ToLower()) == action.AddOrKill select p;
   }
   

   This approach is much better but I would go even further. Instead of this if blocks I would use dictionaries which will map input string into corresponding delegates .
   It has nothing to do with LINQ yet, only common sense. This was the main part. My other points are:

2. UpperCamelCase?

3. action.AddOrKill looks very strange. It took me a while to understand what are you trying to achieve with it. It looks very weird and should be rewritten in some other, more developer-friendly way.

4. I would not use such a method signature - void with ref parameter. It doesn't look solid with existing LINQ methods and there is no point in having such a signature. I would use regular LINQ IQueryable MethodName(IQueryable<> source, ...other parameters).

5. I believe in such cases you should create fluent interface - it will improve code readability a lot. Especially in implementing NOT functionality.

Answer 2

I don't see anything LINQ related that could be done here... but it looks to me like all the contents of all the IF statements are the same. Perhaps that should be extracted into a method?

Also:

ToLower().StartsWith(actionValue) == action.AddOrKill

Should be

StartsWith(actionValue, StringComparison.CurrentCultureIgnoreCase) == action.AddOrKill 

Using ToLower for case insensitive string comparisons doesn't respect the culture of the current system, and will give wrong results for several languages (particularly those with 3 cases rather than 2). Plus, it's slower, because you're converting the entire string, rather than simply running comparisons on the beginning of the string.

---

EDIT: I see the difference between the IF blocks now. It's been a while since I've played with C#, so I'm not entirely sure this will compile out of the box, but it should give you the idea. Basically you pass around a functor rather than replicating the if block over and over again:

private void applyFilterAction(ref IQueryable<TempArticle> products, FilterAction action)
{
    var actionValue = action.Value.ToLower();
    var column = action.Column;
    Func<string, string, bool> comparer;

    if (action.Action.Equals("StartsWith"))
    {
        comparer = (a, b) => a.StartsWith(b, StringComparison.CurrentCultureIgnoreCase);
    }
    else if (action.Action.Equals("EndsWith"))
    {
        comparer = (a, b) => a.EndsWith(b, StringComparison.CurrentCultureIgnoreCase);
    }
    else if (action.Action.Equals("Contains"))
    {
        comparer = (a, b) => a.Contains(b, StringComparison.CurrentCultureIgnoreCase);
    }
    else if (action.Action.Equals("Exact"))
    {
        comparer = (a, b) => a.Equals(b, StringComparison.CurrentCultureIgnoreCase);
    }

    if (column == "Description")           products = from p in products where comparer(p.Description, actionValue) == action.AddOrKill select p;
    else if (column == "LongDescription")  products = from p in products where comparer(p.LongDescription, actionValue) == action.AddOrKill select p;
    else if (column == "Provider")         products = from p in products where comparer(p.Provider, actionValue) == action.AddOrKill select p;
    else if (column == "ProviderCode")     products = from p in products where comparer(p.ProviderCode, actionValue) == action.AddOrKill select p;
    else if (column == "Publisher")        products = from p in products where comparer(p.Publisher, actionValue) == action.AddOrKill select p;
    else if (column == "PublisherCode")    products = from p in products where comparer(p.PublisherCode, actionValue) == action.AddOrKill select p;
    else if (column == "Custom1")          products = from p in products where comparer(p.Custom1, actionValue) == action.AddOrKill select p;
    else if (column == "Custom2")          products = from p in products where comparer(p.Custom2, actionValue) == action.AddOrKill select p;
    else if (column == "Custom3")          products = from p in products where comparer(p.Custom3, actionValue) == action.AddOrKill select p;
    else if (column == "EanCode")          products = from p in products where comparer(p.EanCode, actionValue) == action.AddOrKill select p;
}

Like I said, it's been a while since I've messed with C#, so this might not work :)

Answer 3

If you can edit the TempArticle class, the best way would be to add an indexer to the class that switches on the column name and returns the value of that column. Your filter code would then be

private void applyFilterAction(ref IQueryable<TempArticle> products, FilterAction action)
{
    if (action.Action == "StartsWith")
    {
        products = from p in products where p[action.Column].StartsWith(action.Value, StringComparison.CurrentCultureIgnoreCase) == action.AddOrKill select p;
    }
    else
    {
        products = from p in products where p[action.Column].EndsWith(action.Value, StringComparison.CurrentCultureIgnoreCase) == action.AddOrKill select p;
    }
}

In case you can't change TempArticle's definition and assuming action.Column equals the name of the corresponding property in TempArticle, you can use compiled Expressions as accessors:

static class PropertyAccessor<T>
{
    static Dictionary<string, Func<T, object>> propGetters;
    static Dictionary<string, Func<T, object>> PropGetters
    {
        get
        {
            if (propGetters == null)
            {
                Initialize();
            }
            return propGetters;
        }
    }

    static Dictionary<string, Action<T, object>> propSetters;
    static Dictionary<string, Action<T, object>> PropSetters
    {
        get
        {
            if (propSetters == null)
            {
                Initialize();
            }
            return propSetters;
        }
    }

    static void Initialize()
    {
        propGetters = new Dictionary<string, Func<T, object>>();
        propSetters = new Dictionary<string, Action<T, object>>();

        var type = typeof(T);

        foreach (var pi in type.GetProperties(BindingFlags.Public | BindingFlags.GetProperty | BindingFlags.Instance))
        {
            if (pi.CanRead)
            {
                var parameter = Expression.Parameter(type, "instance");

                Expression<Func<T, object>> lambda;

                if (pi.PropertyType.IsValueType)
                {
                    lambda = Expression.Lambda<Func<T, object>>(Expression.TypeAs(Expression.Property(parameter, pi.Name), typeof(object)), parameter);
                }
                else
                {
                    lambda = Expression.Lambda<Func<T, object>>(Expression.Property(parameter, pi.Name), parameter);
                }

                propGetters.Add(pi.Name, lambda.Compile());
            }

            if (pi.CanWrite)
            {
                var paramExpT = Expression.Parameter(type, "instance");
                var paramExpObj = Expression.Parameter(typeof(object), "value");
                var setterLambda = Expression.Lambda<Action<T, object>>(Expression.Call(paramExpT, pi.GetSetMethod(), Expression.ConvertChecked(paramExpObj, pi.PropertyType)), paramExpT, paramExpObj);
                propSetters.Add(pi.Name, setterLambda.Compile());
            }
        }
    }

    public static object Get(T instance, string propName)
    {
        return PropGetters[propName](instance);
    }

    public static string[] GetAccessorKeys
    {
        get
        {
            return PropGetters.Keys.ToArray();
        }
    }

    public static void Set(T instance, string propName, object value)
    {
        PropSetters[propName](instance, value);
    }

    public static string[] SetAccessorKeys
    {
        get
        {
            return PropSetters.Keys.ToArray();
        }
    }
}

applyFilterAction:

private void applyFilterAction(ref IQueryable<TempArticle> products, FilterAction action)
{
    if (action.Action == "StartsWith")
    {
        products = from p in products where ((string)PropertyAccessor<TempArticle>.Get(p, action.Column)).StartsWith(action.Value, StringComparison.CurrentCultureIgnoreCase) == action.AddOrKill select p;
    }
    else
    {
        products = from p in products where ((string)PropertyAccessor<TempArticle>.Get(p, action.Column)).EndsWith(action.Value, StringComparison.CurrentCultureIgnoreCase) == action.AddOrKill select p;
    }
}

The accessors are compiled only once for each type, but are slightly slower than direct access, partly because of the type casting. (You can use string instead of object if you filter by pi.PropertyType in the initialize loop.)

Other than that, I agree with Snowbear on the coding style and pattern use. This interface looks quite counterintuitive and it's definitely never a good idea to name a flag <Action>or<Opposite>.

Part of the accessor code taken from: Fast Dynamic Property Access with C# (Comments)

Edit:

If you want more speed, you should use HyperDescriptor instead: It writes IL directly and is really fast. In .NET 4, you need to fix the security permissions.

Answer 4

I would write some helper extension methods:

public static class FilterActionHelper {
    private static Dictionary<string, Func<string, Func<string, bool>>> actions = new Dictionary<string, Func<string, Func<string, bool>>>() {
        { "StartsWith", x => y => x.StartsWith(y) },
        { "EndsWith", x => y => x.EndsWith(y) },
        { "Contains", x => y => x.Contains(y) }
    };

    private static Dictionary<string, Func<TempArticle, string>> propertySelectors = new Dictionary<string, Func<TempArticle, string>>() {
        { "Description", x => x.Description },
        { "LongDescription", x => x.LongDescription },
        { "Provider", x => x.Provider },
        { "ProviderCode", x => x.ProviderCode },
        { "Publisher", x => x.Publisher },
        { "PublisherCode", x => x.PublisherCode },
        { "Custom1", x => x.Custom1 },
        { "Custom2", x => x.Custom2 },
        { "Custom3", x => x.Custom3 },
    };

    public static bool HasAction(string actionName) {
        return actions.ContainsKey(actionName);
    }

    public static Func<string, bool> GetAction(this string str, string actionName) {
        return actions[actionName](str);
    }

    public static bool HasProperty(string propertyName) {
        return propertySelectors.ContainsKey(propertyName);
    }

    public static string GetProperty(this TempArticle product, string propertyName) {
        return propertySelectors[propertyName](product);
    }
}

which would allow the more succinct code:

private void ApplyFilterAction(ref IQueryable<TempArticle> products, FilterAction action) {
    var actionValue = action.Value.ToLower();
    var column = action.Column;

    if (!FilterActionHelper.HasAction(action.Action)) {
        return;
    }

    if (!FilterActionHelper.HasProperty(column)) {
        return;
    }

    products = from p in products
               where p.GetProperty(column).ToLower().GetAction(action.Action)(actionValue) == action.AddOrKill
               select p;
}

The use of dictionaries makes it easy to add new properties and actions. The extension methods then make it a lot easier to use those dictionaries in a way that reads well.

Another solution you may find easier is to make FilterAction generic:

public class FilterAction<T> {
    public string Value { get; set; }
    public Func<T, string> GetProperty { get; set; }
    public Func<string, string, bool> Action { get; set; }
    public bool AddOrKill { get; set; }

    public bool Matches(T t) {
        var filterValue = Value.ToLower();
        var propertValue = GetProperty(t).ToLower();
        return (Action(propertyValue, filterValue)) == AddOrKill);
    }
}

…

FilterAction<TempArticle> filter = new FilterAction<TempArticle>();
filter.Value = "foo";
filter.GetProperty = x => x.Custom1;
filter.Action = x, y => x.StartsWith(y);
filter.AddOrKill = false;

var products = from p in products where filter.Matches(p) select p;