Select entities where ID in int array - WCF Data Services, LINQ

Question

I would like to return a set of entities who has and ID that is contained in a list or array of IDs using LINQ and Data Services. I know how to this using LinqToEF but I am at a loss how to this with Data Services or using OData query conventions for that matter.

My thought is that I would do something like:

int[] intArray = {321456, 321355, 218994, 189232};
var query = (from data in context.Entity
             where intArray.contains(data.ID)
             select data);

Is there any way to accomplish using Data Services / OData? I know I could probably hack it with a Service Operation but I would prefer not to do that.

Cheers.

Piecework

Answer 1

Currently OData (the underlying protocol) doesn't support the Contains operation. So that's why the client library does not translate the above query. People are basically using two ways to overcome this limitation: 1) Use service operations as you noted. 2) Construct a where clause dynamically which uses simple comparisons to compare the value to each item from the array. So if the array contains 1, 2, 3, the where would be data.ID == 1 || data.ID == 2 || data.ID == 3 The #2 solution is nice because it's a client side only change. The downside is, that it only works for small arrays. If the array contains too many items the expression gets too long and that leads to all kinds of troubles. The #1 solution doesn't have the size problem, but you need to provide the operation on the server.

Baroque

"Construct a where clause dynamically which uses simple comparisons to compare the value to each item from the array" - can you provide an example. – Rah

Example would be rather long - you can probably search for "dynamic LINQ" and such. The low-level way is to use the Expression class and its API to construct the LINQ query programatically. – Baroque

Answer 2

Here is my realization of WhereIn() Method, to filter IQueryable collection by a set of selected entities:

 public static IQueryable<T> WhereIn<T,TProp>(this IQueryable<T> source, Expression<Func<T,TProp>> memberExpr, IEnumerable<TProp> values) where T : class
    {
        Expression predicate = null;
        ParameterExpression param = Expression.Parameter(typeof(T), "t");

        bool IsFirst = true;

        // Create a comparison for each value eg:                 
        // IN:  t => t.Id == 1 | t.Id == 2                

        MemberExpression me = (MemberExpression) memberExpr.Body;
        foreach (TProp val in values)
        {
            ConstantExpression ce = Expression.Constant(val);


            Expression comparison = Expression.Equal(me, ce);

            if (IsFirst)
            {
                predicate = comparison;
                IsFirst = false;
            }
            else
            {
                predicate = Expression.Or(predicate, comparison);
            }
        }

        return predicate != null
            ? source.Where(Expression.Lambda<Func<T, bool>>(predicate, param)).AsQueryable<T>()
            : source;
    }

And calling of this method looks like:

IQueryable<Product> q = context.Products.ToList();

var SelectedProducts = new List<Product>
{
  new Product{Id=23},
  new Product{Id=56}
};
...
// Collecting set of product id's    
var selectedProductsIds = SelectedProducts.Select(p => p.Id).ToList();

// Filtering products
q = q.WhereIn(c => c.Product.Id, selectedProductsIds);

Clan

Very nice. It does however give an unexpected result with an empty array as input. You'd expect nothing to be in that empty array resulting in no results, but instead it returns everything. With that in mind I've changed the return statement to: return predicate != null ? source.Where(Expression.Lambda<Func<T, bool>>(predicate, param)).AsQueryable<T>() : source.Where(x => false).AsQueryable<T>(); – Underthrust

Answer 3

Thank you men you really helped me :) :)

I did it like Vitek Karas said.

1) Download the Dynamic query library Check this link

No need to read it just download the Dynamic query library

2)Check the project named DynamicQuery. In it you will find a class named Dynamic.cs . Copy It to your project

3)Generate your project( If you are using silverlight an error that say ReaderWriterLock is not found will appear. Don't be affraid. Just comment or delete the lines that make errors( there is just 6 or 7 lines that make errors) )

4) All done you just need now to write your query Example: ordersContext.CLIENTS.Where(" NUMCLI > 200 || NUMCLI < 20");

All done. If you have to use the 'Contains' method you just to write a method that iterate over your array and return the string that your request will use.

    private string MyFilter()
{    string st = "";

       foreach(var element in myTab)
       {
              st = st + "ThePropertyInTheTable =" + element + "||"; 
        }

        return st;
}

I hope you understand me and that i helped someone :)

Riannon

This solution lead to a problem when the request was too long. So I found another solution using AddQueryOption and using $filter like this: DataServiceQuery<CLIENT> ordersQuery = (DataServiceQuery<CLIENT>)this.context.CLIENTS.AddQueryOption("$filter", MyFilter()); – Riannon

Dont use the uppercase AND. it leads to and error. use and not AND. – Riannon