LINQ to Entities Does Not Recognize Boolean Contains [Int32]

Question

I have the following extension methods in which I use for Content in LINQ-To-Entities:

public static class Extensions { 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); } } 

When I call the extenion method to check if the list of identifiers is in a specific table, it works, and I return a list of identifiers, for example:

 List<int> Ids = _context.Persons .WhereIn(x => x.PersonId, PersonIds) .Select(x => x.HeaderId).ToList(); 

When I execute the following statement, he complains that LINQ-To-Entities is not recogonize Contains (int32), but I thought that I would no longer be against the entity, but a set of ints.

 predicate = predicate.And(x=> Ids.Contains(x.HeaderId)); 

If I have a comma delimited string such as "1,2,3", then the following works:

 predicate = predicate.And(x=>x.Ids.Contains(x.HeaderId)); 

I am trying to return a list and create a list of strings separated by commas, the problem is that now when I do predicate = predicate.And(x=>sb.Contains(x.HeaderId.ToString()); he complains that he don't like ToString ().

I also tried doing:

 predicate = predicate.And(x=>Extensions.WhereIn(Ids, x.id));, but it can't resolve WhereIn. It says I must add `<>`, but I am not sure what to add here and how implement it. 

Answer 1

Where there is nothing wrong with your WhereIn , and you are right: when you use Identifiers , you are no longer going against the object, but collecting ints.

The problem is that you are using .And for the predicate: LINQ-To-Entities is trying to convert everything inside these brackets into Entities methods, and there are no corresponding Contains method.

Decision:

Instead

 predicate = predicate.And(x=> Ids.Contains(x.HeaderId)); 

using

 predicate = predicate.And(Contains<XClassName, int>(x.HeaderId)); 

where Contains the following values:

 private static Expression<Func<TElement, bool>> Contains<TElement, TValue>(Expression<Func<TElement, TValue>> valueSelector, List<TValue> values) { if (null == valueSelector) { throw new ArgumentNullException("valueSelector"); } if (null == values) { throw new ArgumentNullException("values"); } if (!values.Any()) return e => false; var equals = values.Select(value => (Expression)Expression.Equal(valueSelector.Body, Expression.Constant(value, typeof(TValue)))); return Expression.Lambda<Func<TElement, bool>>(@equals.Aggregate(Expression.Or), valueSelector.Parameters.Single()); } 

and XClassName is the class name of your x

Answer 2

You cannot use such an array, you need to use this lambda to expand it to primitives. Alternatively, you can change the underlying provider so that it knows how to generate the IN statement, since it does not work by default.

I did not find a message in which his guys actually implement it, will be updated as soon as I did it.

Basically, when you use your extension method, it looks like

 x=>arr.Contains(x) 

So, if you try to execute such a lambda again with your subsystem, etc., you will get an exception saying that parameters can only be primitives.

The reason is because the underlying provider does not know how to convert the .Contains method for an array as a function parameter to an sql query. And in order to solve this, you have two possibilities.

• teach him how to use T [] as a parameter and use Contains with this parameter

• update your extension method to create a new lamda that will use "allowed" building blocks, that is, expressions using primitive types such as int, string, guid, etc.

Check out this article.

http://msdn.microsoft.com/en-us/library/bb882521(v=vs.90).aspx

Answer 3

Replace:

 List<int> Ids = _context.Persons .WhereIn(x => x.PersonId, PersonIds) .Select(x => x.HeaderId).ToList(); 

with

 var Ids = _context.Persons .WhereIn(x => x.PersonId, PersonIds) .Select(x => x.HeaderId).ToList(); 

and then try.