By James Bloomer


2008-12-17 11:24:20 8 Comments

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".

10 comments

@Rob Fonseca-Ensor 2009-01-01 06:03:57

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) + "}");

@Robert Claypool 2009-02-20 13:48:36

Do you have any more info on "it"? The "it" prefix shows up in MSDN samples, but nowhere can I find an explanation about when/why "it" is needed.

@Shimmy 2010-01-25 08:26:59

Used in Entity Framework dynamic query, take a look at geekswithblogs.net/thanigai/archive/2009/04/29/…, Thanigainathan Siranjeevi explains it there.

@smg 2015-06-11 07:38:29

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.

@Shimmy 2009-07-01 15:29:11

Update: EF ≥ 4 supports Contains directly (Checkout Any), so you don't need any workaround.

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");
  if (!collection.Any()) 
    return query.Where(t => false);

  ParameterExpression p = selector.Parameters.Single();

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

//Optional - to allow static collection:
public static IQueryable<TEntity> WhereIn<TEntity, TValue>
  (
    this ObjectQuery<TEntity> query,
    Expression<Func<TEntity, TValue>> selector,
    params TValue[] collection
  )
{
  return WhereIn(query, selector, (IEnumerable<TValue>)collection);
}

USAGE:

public static void Main()
{
  using (MyObjectContext context = new MyObjectContext())
  {
    //Using method 1 - collection provided as collection
    var contacts1 =
      context.Contacts.WhereIn(c => c.Name, GetContactNames());

    //Using method 2 - collection provided statically
    var contacts2 = context.Contacts.WhereIn(c => c.Name,
      "Contact1",
      "Contact2",
      "Contact3",
      "Contact4"
      );
  }
}

@Marc Gravell 2010-01-05 06:12:30

("related question" comment removed, as it was deleted by the author)

@SDReyes 2010-02-23 19:48:10

marked for further reading +1... very creative : D

@Julien N 2010-05-11 12:38:12

Really nice, elegant... Works well.

@Shimmy 2010-05-12 10:40:50

Another good idea would be having the same function declaring the collection parameter as a paramarray TValue[]. this would give you unlimited control so you can manually specify items as the collection, I will elaborate more if needed.

@dudeNumber4 2010-08-23 15:48:46

Warning; when arg is large collection (mine was 8500 item int list), stack overflow. You may think it crazy to pass such a list, but I think this exposes a flaw in this approach, nonetheless.

@Nap 2010-09-22 07:05:52

Correct me if I am wrong. but this means when the passed collection (filter) is an empty set it will basically result in all the data cause it just returned the query param. I was expecting it to filter all value, is there a way to do this?

@Shimmy 2010-09-22 08:59:08

If you mean that when the checking collection is empty it should return no results, the in the above snippet replace the if (!collection.Any()) //action; - replace action with simply returning an empty query of the requested type for best performance - or just remove this line.

@Antoine Aubry 2011-01-26 15:03:15

return WhereIn(query, selector, collection); should be replaced by return WhereIn(query, selector, (IEnumerable<TValue>)collection); to avoid unwanted recursion.

@grimus 2011-05-10 20:09:05

Can you provide a link to documentation verifying that Contains is supported in EF4? I'm having trouble tracking it down. Thanks!

@Shimmy 2011-05-10 20:39:40

@grimus, I tested it and it works, what's the trouble you're having.

@grimus 2011-05-10 21:29:14

@Shimmy I'm just looking to see if it was documented any where. I'm not having any problems.

@Shimmy 2011-05-10 22:03:02

@grimus, I once saw it documented in a connection and was marked "as fixed", but couldn't get to it.

@ShadowChaser 2012-02-29 19:06:21

I believe there's a bug in the code. If the supplied list of values is empty, the correct behavior should be to return no results - ie/ no objects in the query exist in the collection. However, the code does the exact opposite - all values are returned, not none of them. I believe you want "if (!collection.Any()) return query.Where(e => false)"

@Shimmy 2012-02-29 23:27:39

@ShadowChaser, makes sense. answer updated.

@Xaisoft 2013-05-30 16:34:38

@Shimmy - How do I do something like this where I have to explicitly specify the types. predicate = predicate.And(x=>Extensions.WhereIn<>(x.id,ids));. x is a Person Entity, and id and ids are both strings. I am not sure what to put between the brackets of WhereIn.

@avenmore 2014-07-15 12:39:42

What changes would be required to create a "WhereNotIn()" method?

@Romain Vergnory 2015-08-18 13:00:16

@dudeNumber4 did you find any workaround to this issue ?

@dudeNumber4 2015-08-20 13:41:57

@RomainVergnory Looking back into that code, it looks like I ended up using a stored proc in Sql Server. I passed the list of ints into the sproc as a comma separated string (varchar max) which I turned into a back into a set inside the sproc. Hack for sure.

@Shannon 2010-04-30 04:51:05

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.

@James Bloomer 2009-12-23 09:28:12

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

@GabrielC 2009-08-26 08:31:51

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

@jrojo 2009-07-23 14:39:32

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

@Phani Raj 2008-12-24 22:44:00

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

@James Bloomer 2008-12-17 14:31:22

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

@Merritt 2009-06-25 15:11:42

If you want to do a 'Not contains', just make the following edits in the BuildContainsExpression method: - Expression.Equal becomes Expression.NotEqual - Expression.Or becomes Expression.And

@AndreasN 2008-12-17 11:41:59

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;

@Jon Skeet 2008-12-17 11:44:24

Any doesn't take an object of the sequence type - it either has no parameters (in which case it's just "is this empty or not") or it takes a predicate.

@SDReyes 2010-02-23 19:59:37

I'm terribly glad to have found this answer : ) +1 Thanks AndreasN

Related Questions

Sponsored Content

34 Answered Questions

[SOLVED] What is the Java equivalent for LINQ?

  • 2009-08-01 18:53:26
  • Ahmed
  • 268076 View
  • 805 Score
  • 34 Answer
  • Tags:   java linq

24 Answered Questions

[SOLVED] Case insensitive 'Contains(string)'

23 Answered Questions

[SOLVED] LINQ query on a DataTable

13 Answered Questions

[SOLVED] When to use .First and when to use .FirstOrDefault with LINQ?

  • 2009-06-21 19:15:29
  • Metro Smurf
  • 537287 View
  • 796 Score
  • 13 Answer
  • Tags:   c# .net linq

7 Answered Questions

[SOLVED] Multiple "order by" in LINQ

  • 2008-11-18 13:34:11
  • Sasha
  • 574793 View
  • 1534 Score
  • 7 Answer
  • Tags:   linq sql-order-by

9 Answered Questions

[SOLVED] Group by in LINQ

  • 2011-09-06 19:44:20
  • test123
  • 1214407 View
  • 1000 Score
  • 9 Answer
  • Tags:   c# linq group-by

8 Answered Questions

16 Answered Questions

[SOLVED] Entity Framework vs LINQ to SQL

9 Answered Questions

[SOLVED] Concat all strings inside a List<string> using LINQ

  • 2009-02-18 00:56:57
  • Jobi Joy
  • 387621 View
  • 509 Score
  • 9 Answer
  • Tags:   c# linq .net-3.5

4 Answered Questions

Sponsored Content