'Contains()' workaround using Linq to Enti

Question

I'm trying to create a query which uses a list of ids in the where clause, using the Silverlight ADO.Net Data Services client api (and therefore Linq To Entities). Does anyone know of a workaround to Contains not being supported?

I want to do something like this:

List<long?> txnIds = new List<long?>();
// Fill list 

var q = from t in svc.OpenTransaction
        where txnIds.Contains(t.OpenTransactionId)
        select t;

---

Tried this:

var q = from t in svc.OpenTransaction
where txnIds.Any<long>(tt => tt == t.OpenTransactionId)
select t;

But got "The method 'Any' is not supported".

Answer 1

In addition to selected answer.

Replace Expression.Or with Expression.OrElse to use with Nhibernate and fix Unable to cast object of type 'NHibernate.Hql.Ast.HqlBitwiseOr' to type 'NHibernate.Hql.Ast.HqlBooleanExpression' exception.

Answer 2

I'm not sure about Silverligth, but in linq to objects i always use any() for these queries.

var q = from t in svc.OpenTranaction
        where txnIds.Any(t.OpenTransactionId)
        select t;

Answer 3

Here's an example where I demonstrate how to write set-based queries using the DataServiceContext : http://blogs.msdn.com/phaniraj/archive/2008/07/17/set-based-operations-in-ado-net-data-services.aspx

Answer 4

You can fall back on hand coding some e-sql (note the keyword "it"):

return CurrentDataSource.Product.Where("it.ID IN {4,5,6}"); 

Here is the code that I used to generate some e-sql from a collection, YMMV:

string[] ids = orders.Select(x=>x.ProductID.ToString()).ToArray();
return CurrentDataSource.Products.Where("it.ID IN {" + string.Join(",", ids) + "}");

Answer 5

I think a Join in LINQ can be a walkaround.

I haven't tested the code though. Hope it helps. Cheers. :-)

List<long?> txnIds = new List<long?>();
// Fill list 

var q = from t in svc.OpenTransaction
        join tID in txtIds on t equals tID
        select t;

Join in LINQ:

http://weblogs.asp.net/salimfayad/archive/2008/07/09/linq-to-entities-join-queries.aspx

Answer 6

From MSDN:

static Expression<Func<TElement, bool>> BuildContainsExpression<TElement, TValue>(
    Expression<Func<TElement, TValue>> valueSelector, IEnumerable<TValue> values)
{
    if (null == valueSelector) { throw new ArgumentNullException("valueSelector"); }
    if (null == values) { throw new ArgumentNullException("values"); }
    ParameterExpression p = valueSelector.Parameters.Single();

    // p => valueSelector(p) == values[0] || valueSelector(p) == ...
    if (!values.Any())
    {
        return e => false;
    }

    var equals = values.Select(
             value => (Expression)Expression.Equal(valueSelector.Body, Expression.Constant(value, typeof(TValue))));

    var body = equals.Aggregate<Expression>((accumulate, equal) => Expression.Or(accumulate, equal));

    return Expression.Lambda<Func<TElement, bool>>(body, p);
} 

and the query becomes:

var query2 = context.Entities.Where(BuildContainsExpression<Entity, int>(e => e.ID, ids));