'Contains ()' workaround using Linq for Entities?

Question

I am trying to create a query that uses the list of identifiers in the where clause using Apache Silverlight ADO.Net Data Services (and therefore Linq To Entities). Does anyone know of a workaround to Contains not 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 received the "Method" Any "is not supported."

Answer 1

Update: EF ≥ 4 supports Contains directly (Checkout Any ), so you don't need a 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); } 

APPLICATION:

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

Answer 2

You can again take the coding side of some e-sql (note the "it" keyword):

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

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

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

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 request will look like this:

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

Answer 4

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

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

Answer 5

To complete the recording, here is the code that I finally used (to check the clarity of the error) ...

 // 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 6

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

Answer 7

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

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

Generated by:

 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 8

I think joining in LINQ might be a walkway.

I have not tested the code yet. Hope it helps. Greetings. :-)

 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 LINQ:

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

Answer 9

Sorry new user, I would comment on the actual answer, but it seems that I still can not do this?

In any case, regarding the response with sample code for BuildContainsExpression (), keep in mind that if you use this method for database objects (i.e. not for objects in memory) and you use IQueryable, then actually this should disappear into the database, since it basically satisfies a lot of SQL conditions or conditions for checking the where-in clause (run it using the SQL profiler).

This may mean that if you clear IQueryable with multiple BuildContainsExpression (), it will not include it in a single SQL statement, which will run at the end, as you expect.

The workaround for us was to use multiple LINQ connections to support it in a single SQL call.

Answer 10

In addition to the selected answer.

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