Working around LinqToSQls "queries with local collections are not supported" exception

Question

So, I'm trying to return a collection of People whose ID is contained within a locally created collection of ids ( IQueryable)

When I specify "locally created collection", I mean that the Ids collection hasnt come from a LinqToSql query and has been programatically created (based upon user input). My query looks like this:

var qry = from p in DBContext.People
                  where Ids.Contains(p.ID)
                  select p.ID;

This causes the following exception...

"queries with local collections are not supported"

How can I find all the People with an id that is contained within my locally created Ids collection?

Is it possible using LinqToSql?

Answer 1

If Ids is a List, array or similar, L2S will translate into a contains.

If Ids is a IQueryable, just turn it into a list before using it in the query. E.g.:

List<int> listOfIDs = IDs.ToList();  
var query =  
from st in dc.SomeTable  
where listOfIDs.Contains(st.ID)
select .....

Answer 2

I was struggling with this problem also. Solved my problem with using Any() instead

people.Where(x => ids.Any(id => id == x.ID))

Answer 3

As the guys mentioned above, converting the ids, which is of type IQueryable to List or Array will solve the issue, this will be translated to "IN" operator in SQL.But be careful because if the count of ids >= 2100 this will cause another issue which is "The server supports a maximum of 2100 parameters" and that is the maximum number of parameters(values) you can pass to "IN" in SQL server.

Another alternative would be keeping ids as IQueryable and using LINQ "Any" operator instead of "Contains", this will be translated to "EXISTS" in SQL server.