'Contains()' workaround using Linq to Entities?
C#LinqEntity Framework.Net 3.5Linq to-EntitiesC# Problem Overview
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".
C# Solutions
Solution 1 - C#
Update: EF ≥ 4 supports [Contains
](http://msdn.microsoft.com/en-us/library/bb352880.aspx "Enumerable.Contains<T>") directly (Checkout [Any
](http://msdn.microsoft.com/en-us/library/bb534972.aspx "Enumerable.Any<T>")), 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"
);
}
}
Solution 2 - C#
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) + "}");
Solution 3 - C#
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));
Solution 4 - C#
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;
Solution 5 - C#
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);
}
Solution 6 - C#
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
Solution 7 - C#
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])
Solution 8 - C#
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
Solution 9 - C#
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
This can mean, if you are refining an IQueryable
The workaround for us was to use multiple LINQ joins to keep it to one SQL call.
Solution 10 - C#
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.