Where IN and NOT IN Queries with WCF Data Services

It's a common requirement to want to select a list of items where the selection criteria matches any value in another list. The following article shows why it's not easy to do this with WCF Data Services out of the box and how to create extension methods to provide this functionality.

If comparing two lists in code you might do something like this: 

var res = List1.Where(n => !List2.select(n1 => n1.Id).Contains.(n.Id)).ToList(); 

When querying using Entity Framework there is an additional step  to create a list of Ids  as the .Contains() method needs to be called on a List of a primitive type (or you'll get an error along the lines of: Unable to create a constant value of type 'Model.Entities.MyEntity'. Only primitive types ('such as Int32, String, and Guid') are supported in this context).

var res = context.MyEntities.Where(ent => !Ids.Contains(ent.Id) ).ToList(); 

With WCF Data Services it is not possible to use .Contains() at all. This time the error returned will be something like: "The expression value(System.Collections.Generic.List`1[System.Int32]).Contains([10007].Id) is not supported". Pretty much the only option is to create WHERE predicate which checks for each Id in Ids:

Below is an extension method to create the predicate (and also for a NOT IN predicate). It also allows for the comparison to be made using multiple parameters.

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Linq.Expressions;
using System.Reflection;

namespace WcfDataServiceHelper
{
    public static class QueryExtensions
    {

        public static IQueryable<T> WhereIn<T>(this IQueryable<T> source, IEnumerable<dynamic> filterList) where T : class
        {
            return InternalWhereIn<T>(source, filterList, false);
        }

        public static IQueryable<T> WhereNotIn<T>(this IQueryable<T> source, IEnumerable<dynamic> filterList) where T : class
        {
            return InternalWhereIn<T>(source, filterList, true);
        }

        private static IQueryable<T> InternalWhereIn<T>(this IQueryable<T> source, IEnumerable<dynamic> filterList, bool notIn) where T : class
        {

            //Check all properties in the filter list are on T
            var SourcePropertyInfos = typeof(T).GetProperties();

            //get the anonymous property infos for the filterList
            var filterListPropertyInfos = filterList.GetType().GetGenericArguments().First(ga => ga.Name.Contains("AnonymousType")).GetProperties();

            if (filterListPropertyInfos.Count() == 0)
                throw new ArgumentException("Not properties found on filter list");

            //check that the filterList property names match up with properties on T
            foreach (PropertyInfo flpi in filterListPropertyInfos)
            {
                if (!SourcePropertyInfos.Any(spi => spi.Name == flpi.Name && spi.PropertyType == flpi.PropertyType))
                    throw new ArgumentException("Property " + flpi.Name + ", with type of: " + flpi.PropertyType + " not found on source type");
            }

            Expression predicate = null;

            ParameterExpression param = Expression.Parameter(typeof(T), "t");

            foreach (var filter in filterList)
            {
                bool IsFirst = true;

                Expression filterPredicate = null;

                /*
                 * Create a comparison for each property eg:                 
                 * IN:  t => t.Id == 1 & t.Id1 == 2                
                 * NOT IN:  t => t.Id != 1 | t.Id1 != 2
                 */
                foreach (PropertyInfo flpi in filterListPropertyInfos)
                {
                    MemberExpression me = Expression.Property(param, flpi.Name);

                    ConstantExpression ce = Expression.Constant(flpi.GetValue(filter, null));

                    Expression comparison;

                    if (notIn)
                    {
                        comparison = Expression.NotEqual(me, ce);
                    }
                    else
                    {
                        comparison = Expression.Equal(me, ce);
                    }

                    if (IsFirst)
                    {
                        if (notIn)
                        {
                            filterPredicate = (filterPredicate == null) ? comparison : Expression.Or(filterPredicate, comparison);
                        }
                        else
                        {
                            filterPredicate = (filterPredicate == null) ? comparison : Expression.Or(filterPredicate, comparison);
                        }

                        IsFirst = false;
                    }
                    else
                    {
                        if (notIn)
                        {
                            filterPredicate = Expression.Or(filterPredicate, comparison);
                        }
                        else
                        {
                            filterPredicate = Expression.And(filterPredicate, comparison);
                        }
                    }
                }

                /*add the filterPredicate to the final predicate eg:
                 * IN:  (t => t.Id == 1 & t.Id1 == 2) | (t => t.Id == 2 & t.Id1 == 3)
                 * NOT IN:  (t => t.Id != 1 | t.Id1 != 2) &  (t => t.Id != 2 | t.Id1 != 3)
                 */
                if (notIn)
                {
                    predicate = (predicate == null) ? filterPredicate : Expression.And(predicate, filterPredicate);
                }
                else
                {
                    predicate = (predicate == null) ? filterPredicate : Expression.Or(predicate, filterPredicate);
                }
            }

            return source.Where(Expression.Lambda<Func<T, bool>>(predicate, param)).AsQueryable<T>();
        }
    }
}

The method takes a List as the filter parameter so select the properties to use in the comparison between the two lists in to a new Anonymous type as below. The property names  and types on the Anonymous type must match the property names and types on the entity.

var checkList = myEntities.Select(entity => new { Id = entity.Id, Id2 = entity.Id2 }).ToList();
var res = repository.MyEntities.WhereIn(checkList).ToList();

Example code is available to download here.