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

---

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

Ericaericaceous

Note: Entity Framework 4 (in .NET 4) has a "Contains" method, just in case someone happens to be reading this that doesn't know about it. I know the OP was using EF1 (.NET 3.5). – Peng

@Darrell I just wasted a half an hour because I skipped over your comment. I wish I could make your comment blink and marquee across the screen. – Probity

Answer 1

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

Unsavory

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

Really nice, elegant... Works well. – Abscission

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. – Unsavory

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. – Jackfish

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? – Verein

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. – Unsavory

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

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

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

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

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

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)" – Honea

@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. – Benniebenning

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

@Jackfish did you find any workaround to this issue ? – Alisha

@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. – Jackfish

Answer 2

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

Rheims

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. – Bayberry

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

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

Ericaericaceous

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 – Sleekit

Answer 4

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;

Mezzorilievo

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. – Jeminah

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

Answer 5

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

Ericaericaceous

Answer 6

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

Coagulate

Answer 7

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

Oxy

Answer 8

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

Cherilyncherilynn

Answer 9

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.

Saintjust

Answer 10

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.

Father