Scope of Collections used in LINQ Predicates

Question

I really like PredicateBuilder. It allows me to build all sorts of queries very dynamically. The predicate variable can be passed around to different objects and they can add onto it with values they know about, etc. Except when I am needing to use a .Contains on a hashed collection. Bzzt! Crash and burn.

For instance (example/pseudo code, this may or may not compile/run):

protected Expression<Func<MyClass, bool>> GetWherePredicate()
{
    string[] selectedValues = Request.Form.GetValues("checkbox1") ?? new string[0];
    HashSet<int> selectedIDs = new HashSet<int>(selectedValues.Cast<int>());

    Expression<Func<MyClass, bool>> predicate = PredicateBuilder.True<MyClass>();
    predicate = predicate.And(s => selectedIDs.Contains(s.ID));

    return predicate;
}

protected void Retrieve()
{
    Expression<Func<MyClass, bool>> predicate = GetWherePredicate();
    IEnumerable<MyClass> retrievedValues = MyDataContext.GetTable<MyClass>.Where(predicate);
}

When I try to do that, I get a NotSupportedException: Method 'Boolean Contains(Int32)' has no supported translation to SQL due to the selectedIDs HashSet not being in scope. If I do this all in the same method, then it works fine.

I need to know the right way to get my predicate there to resolve or compile or whatever so that it can be used in a different scope from where the HashSet is declared. Any help?

UPDATE: I had this pretty wrong. The code below works fine, so there is no scope conflict. Thanks Jay.

string[] selectedValues = Request.Form.GetValues("checkbox1") ?? new string[0];
Expression<Func<MyClass, bool>> predicate = PredicateBuilder.True<MyClass>();
predicate = predicate.And(s => selectedValues.Contains(s.ID.ToString()));

Answer 1

From the exception you cite, it seems unlikely that scope is a factor here.

My answer to this is that you need to declare selectedIDs as IEnumerable<int> instead of HashSet<int> (or just cast it before calling Contains(), but that doesn't account for it working when all in the same method, so I'm unsure.

Without seeing any actual code that is exhibiting this behaviour, it will be difficult to troubleshoot any further.

Answer 2

Do not get razzle-dazzled by the name Contains... there are many methods that are named that, and not many have supported translations into SQL.

• Enumerable.Contains<T> and List<T>.Contains are methods with supported translations.
• HashSet<T>.Contains is a method that has no supported translation.

---

There are many resolutions, but I recommend:

IEnumerable<string> selectedValueQuery =
  Request.Form.GetValues("checkbox1") ?? Enumerable.Empty<string>();
List<string> selectedIds = selectedValueQuery
  .Cast<int>()
  .Distinct()
  .ToList();

Although a simpler solution might be:

IEnumerable<int> selectedIDs = new HashSet<int>(selectedValues.Cast<int>()); 

---

Edit: It's not about concrete implementation of Contains at all. It's about whether the linqtosql query provider can identify the method and translate it into an IN (list) sql expression. The recognition code looks at the type of the parameter used in the expression. The recognition code does not use polymorphism/implementation nor does it walk the inheritence tree looking for other possibilities.

List<int> myList = new List<int>(){1, 2, 3};
IList<int> myIList = myList;
IEnumerable<int> myIEnumerable = myList;

  //works by List<T>.Contains()
db.Customers.Where(c => myList.Contains(c.CustomerID));

  //doesn't work, no translation for IList<T>.Contains
db.Customers.Where(c => myIList.Contains(c.CustomerID));

  //works by Enumerable.Contains<T>()
db.Customers.Where(c => myIEnumerable.Contains(c.CustomerID));

  //works by Enumerable.Contains<T>()
db.Customers.Where(c => Enumerable.Contains(myIEnumerable, c.CustomerID));

Even though these parameters reference the same instance, different translation behaviors occur because the type of the parameter is different.

.Contains() is not called as it is translated, therefore its implementation is irrelevant. It could throw NotImplementedException or return true;