Odata Error translating Linq expression at call Contains

Question

Is it possible using ASP.NET Web APi OData make similar:

List<string> customersTitles = Odata.OrdersService.Select(o=>o.CustomerTitle).Distinct().ToList();

List<Customer> customers = Odata.CustomerService.Where(m => customersTitles .Contains(m.CustomerTitle))

Get error:

Error translating Linq expression to URI: The expression value(System.Collections.Generic.List`1[System.String]).Contains([10007].CustomerTitle) is not supported.}

API:

public class CustomerController : EntitySetController<Customer, int>
{

    [Queryable]
    public override IQueryable<Customer> Get()
    {
        Expression filter = this.QueryOptions.Filter.ToExpression<Customer>();
        return db.Query<Customer>(filter as Expression<Func<Customer, bool>>);
    }
}

Answer 1

The Contains construct is not supported via URIs since the list of strings existing at the client side is not a Server side resource.

Linq2Sql Provider has an innate translation for Contains, which gets translated to IN clause for SQL.

With OData, such a translation is not supported. What you need to build is an expanded query list for your where clause using all the Title values:

Because this does not work:

List<Customer> customers = Odata.CustomerService.Where(m => customersTitles .Contains(m.CustomerTitle))

the expanded query option helps us in building a query like:

List<Customer> customers = Odata.CustomerService.Where(m => m.CustomerTitle == customerTitles[0] || m.CustomerTitle == customerTitles[1]); // and so on

Here is the code for the filter building:

var titleFilterList = customerTitles.Select(title => String.Format("(CustomerTitle eq {0})", title));
var titleFilter = String.Join(" or ", titleFilterList);
var customers = Odata.CustomerService.AddQueryOption("$filter", titleFilter).Execute().ToList(); // you may have to cast this.

There is another option to do the same in a strongly typed manner using a nice extension method and building a dynamic Expression based predicate. Follow the steps from here:

http://blogs.msdn.com/b/phaniraj/archive/2008/07/17/set-based-operations-in-ado-net-data-services.aspx

Answer 2

The following extension method can be used LINQ to perform OData queries that test if the value of a property is contained in a set similar to how Contains works with LINQ to EF. It is based on the link provided in Raja Nadar's answer, specifically the comment at the end by Nick.

public static IQueryable<T> WherePropertyIsIn<T, TSet>(
    this IQueryable<T> query,
    IEnumerable<TSet> set,
    Expression<Func<T, TSet>> propertyExpression
) {
    var filterPredicate = set.Select(value => Expression.Equal(propertyExpression.Body, Expression.Constant(value)))
        .Aggregate<Expression, Expression>(Expression.Constant(false), Expression.Or);

    var filterLambdaExpression = Expression.Lambda<Func<T, bool>>(filterPredicate, propertyExpression.Parameters.Single());

    return query.Where(filterLambdaExpression);
}

Usage:

var allowed_states = getAllowedStates();
var maxPopulation = getMaxPopulation();

// Instead of...
var cities = context.Cities.Where(c => allowed_states.Contains(c.State) && c.Population <= maxPopulation);

// Use...
var cities = context.Cities.Where(c => c.Population <= maxPopulation).WherePropertyIsIn(allowed_states, c => c.Cities);

Note that you must have a separate Where call (as shown above) if you wish to filter by more than WherePropertyIsIn allows. It would be nice if this could be combined into a single Where but I couldn't figure out how.