Creating a dynamic OR statement with Linq to Entities

For a project I am working on, we needed to select items from our custom database filtered on a variable anoumt of taxonomy values. As we use Linq to Entities (for .Net 3.5, as this is a SharePoint 2010 project) to access our database containing entities that store the Guid-Label pair values of the TaxonomyWebTagging control, we needed a way to use 'Contains' with a dynamic set of taxonomy terms (including sub terms).

Looking for a solution to dynamically create an OR statement with multiple 'Contains', there was little to be found. The best option I saw proposed was the option to use something like:

List<int> values = new List<int>(new int[] { 1, 3, 5 });
var items = from i in itemcollection
            where values.Contains(i.Id)
            select i;

This is great if you use Linq to Objects, but doesn't work for Linq to Entities. Then I thought of the option to use a query for the possible other filters, then call the .ToList(), and then you have a list of (Entity) objects that you can query using Linq to Objects. Still, this didn't seem the best solution to me. We could possibly have to deal with a great deal of objects, if the where no other filters selected.

We already used a sample of code with the method 'BuildContainsExpression' found here. This sample already does a lot of what we needed, but does not use a Contains statement, and looks for the items just the other way round. Great for ids, and very useful. As I started experimenting I found that I could use it as a basis for a new method, and before I knew it, it worked like a charm. The SQL generated by this Linq to Entities statement almost looks like how I would write this myself.

To create a sample I have refactored the original code created by me, and made it even more flexible. Also I added an extension method to enable even more easy coding. The method used can be changed, as the method name is contained in a string, no intellisense is possible here, so use you own brainpower, don't be lazy.

You can use method names that are available on the object of the type set at the second generic type 'TValue', and accept one parameter of the same type. This is the type of the property you wish to filter on. Here is the main code:

public static IQueryable<TSource> WhereOr<TSource, TValue>(this IQueryable<TSource> source, 
        Expression<Func<TSource, TValue>> valueSelector, string methodName, IEnumerable<TValue> values)
{
    return source.Where(BuildWhereOrExpression(valueSelector, methodName, values));
}

private static Expression<Func<TElement, bool>> BuildWhereOrExpression<TElement, TValue>
        (Expression<Func<TElement, TValue>> valueSelector, string methodName, IEnumerable<TValue> values)
{
    if (null == valueSelector) { throw new ArgumentNullException("valueSelector"); }
    if (null == values) { throw new ArgumentNullException("values"); }

    ParameterExpression p = valueSelector.Parameters.Single();
    if (!values.Any())
    {
        return e => false;
    }
    var methodInfo = typeof(TValue).GetMethod(methodName, new[] { typeof(TValue) });

    var equals = values.Select(value => (Expression)Expression.Call(valueSelector.Body, methodInfo, 
                                Expression.Constant(value, typeof(TValue))));
    var body = equals.Aggregate<Expression>((accumulate, equal) => Expression.Or(accumulate, equal));
    return Expression.Lambda<Func<TElement, bool>>(body, p);
}

You can then use this for example, by calling:

ctx.DataItems.WhereOr<DataItem, string>(i => i.values, "Contains", new string[] { "123;", "567;", "890;" });

The following query was generated:

SELECT
[Extent1].[id] AS [id],
[Extent1].[name] AS [name],
[Extent1].[values] AS [values]
FROM [DataItems] AS [Extent1]
WHERE ((CHARINDEX(N'123;', [Extent1].[values])) > 0) 
OR ((CHARINDEX(N'567;', [Extent1].[values])) > 0) 
OR ((CHARINDEX(N'890;', [Extent1].[values])) > 0)

If you also use other statements, you will find the the OR statements are grouped as one. You can see this in my third sample.

You can download the sample Visual Studio 2010 solution here, containing the extension method and the implementation of three simple, fully working samples. I hope it is useful to you.