'Contains()' workaround using Linq to Entities?

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;

Answer 1

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 2

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 3

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));

Answer 4

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 5

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 6

Hi , The E-Sql way would work when one is working with the ObjectContext from the Entity Framework . Since JBloomer is working with the ADO.NET Data Services Silverlight client , the context type would be DataServiceContext which serializes Linq Expressions into URIs to the Data Service and not E-Sql.

Answer 7

Could anyone please tell me how would I change BuildContainsExpression method to call string.Contains for partial match instead of Equal operation?

Thank you a lot!

Answer 8

public static IQueryable<TEntity> WhereIn<TEntity, TValue>
     (
         this ObjectQuery<TEntity> query, 
         Expression<Func<TEntity, TValue>> selector, 
         IEnumerable<TValue> collection
     )
{
    if (selector == null) throw new ArgumentNullException("selector");
    if (collection == null) throw new ArgumentNullException("collection");
    ParameterExpression p = selector.Parameters.Single();

    if (!collection.Any()) return query;

  IEnumerable<Expression> equals = collection.Select(value => 
     (Expression)Expression.Equal(selector.Body, 
          Expression.Constant(value, typeof(TValue))));

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

  return query.Where(Expression.Lambda<Func<TEntity, bool>>(body, p));
}

Usage:

public static void Main(string[] args)
{
    using (Context context = new Context())
    {
        //args contains arg.Arg
        var arguments = context.Arguments.WhereIn(arg => arg.Arg, args);   
    }
}

Answer 9

Thanks very much. WhereIn extension method was enough for me. I profiled it and generated the same SQL command to the DataBase as e-sql.

public Estado[] GetSomeOtherMore(int[] values)
{
    var result = _context.Estados.WhereIn(args => args.Id, values) ;
    return result.ToArray();
}

Generated this:

SELECT 
[Extent1].[intIdFRLEstado] AS [intIdFRLEstado], 
[Extent1].[varDescripcion] AS [varDescripcion]
FROM [dbo].[PVN_FRLEstados] AS [Extent1]
WHERE (2 = [Extent1].[intIdFRLEstado]) OR (4 = [Extent1].[intIdFRLEstado]) OR (8 = [Extent1].[intIdFRLEstado])

Answer 10

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 11

To complete the record, here's the code I finally used (error checking omitted for clarity)...

// How the function is called
var q = (from t in svc.OpenTransaction.Expand("Currency,LineItem")
         select t)
         .Where(BuildContainsExpression<OpenTransaction, long>(tt => tt.OpenTransactionId, txnIds));



 // The function to build the contains expression
   static System.Linq.Expressions.Expression<Func<TElement, bool>> BuildContainsExpression<TElement, TValue>(
                System.Linq.Expressions.Expression<Func<TElement, TValue>> valueSelector, 
                IEnumerable<TValue> values)
        {
            if (null == valueSelector) { throw new ArgumentNullException("valueSelector"); }
            if (null == values) { throw new ArgumentNullException("values"); }
            System.Linq.Expressions.ParameterExpression p = valueSelector.Parameters.Single();

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

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

Answer 12

Sorry new user, I would have commented on the actual answer, but it seems I can't do that yet?

Anyway, in regards to the answer with sample code for BuildContainsExpression(), be aware that if you use that method on database Entities (i.e. not in-memory objects) and you are using IQueryable, that it actually has to go off to the database since it basically does a lot of SQL "or" conditions to check the "where in" clause (run it with SQL Profiler to see).

This can mean, if you are refining an IQueryable with multiple BuildContainsExpression(), it won't turn it in to one SQL statement that gets run at the end as you expect.

The workaround for us was to use multiple LINQ joins to keep it to one SQL call.

Answer 13

Use http://www.albahari.com/nutshell/linqkit.aspx