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?

score 40 · Accepted Answer · edited Dec 28 '10 at 19:34

40

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 .....

edited Dec 28 '10 at 19:34

Nate

30,286
23
113
184

answered Jul 05 '09 at 16:24

KristoferA

12,287
1
40
62

2

I think the solution to your problem is more nuanced than this answer. I'm using the same construction (a Contains method on an IQueryable in a WHERE clause) successfully, and LINQ to SQL translates it into a WHERE EXISTS clause in the SQL query. It seems to work in some situations and not others, so you should post how you get your Ids IQueryable, as that may shed some light on the issue. – John Bledsoe Jun 28 '10 at 14:12
1

@jmbledsoe if my answer wasn't clear enough: L2S expects a local IEnumerable (not a deferred IQueryable) passed to Enumerable.Contains. If you pass it a local query, that can not be translated to a SQL query. If you pass it a list/array/etc then it can be translated into a SQL "where ... in (x, y, ..., n)" clause. – KristoferA Jun 28 '10 at 14:21
2

I think I see what you're saying: - Passing a List/Array/etc. is OK b/c it will generate a WHERE ... IN clause. - Passing a deferred IQueryable is OK b/c it will integrate with the current deferred query. - Passing a local IEnumerable is NOT OK, but you can just .ToList() it and it will be fine. – John Bledsoe Jun 28 '10 at 16:05
Yes, that pretty much sums it up. – KristoferA Jun 29 '10 at 01:01
4

Resolving your IQueryables early can cause some serious performance hits. Try using Any() instead of Contains() first. – bnieland May 16 '11 at 03:21
1

If the listOfIDs is hundreds or thousands of items you may want to check @maxlego's answer which worked for me – Simon_Weaver Jul 26 '11 at 07:23
@Simon_Weaver if your list of IDs is very large you may even want to put it in a db table (temp or perm) and join to that. SQL Server joins can do more advanced stuff than where clause index scans/index seeks... – KristoferA Jul 27 '11 at 01:23

score 36 · Answer 2 · answered Mar 14 '11 at 13:25

36

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

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

answered Mar 14 '11 at 13:25

maxlego

4,864
3
31
38

@maxlego And the generated SQL is descent. Thanks – John Gathogo Dec 14 '12 at 04:55

score 4 · Answer 3 · answered Sep 13 '18 at 15:32

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.

score 0 · Answer 4 · answered Aug 03 '12 at 07:14

I'm sorry but the answers here didn't work for me as I'm doing dynamic types further along.

What I did was to use "UNION" in a loop which works great. Here's how:

var firstID = cityList.First().id;
var cities = dc.zs_Cities.Where(c => c.id == firstID);
foreach(var c in cityList)
{
  var tempCity = c;
  cities = cities.Union(dc.zs_Cities.Where(cty => cty.id == tempCity.id));
}

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

4 Answers4

Linked