Linq to SQL problem

Question

I have a local collection of record Id's (integers).

I need to retrieve records that have every one of their child records' ids in that local collection.

Here is my query:

public List<int> OwnerIds { get; private set; }
...
filteredPatches = from p in filteredPatches
                  where OwnerIds.All(o => p.PatchesOwners.Select(x => x.OwnerId).Contains(o))
                  select p;

I am getting this error:

Local sequence cannot be used in Linq to SQL implementation of query operators except the Contains() operator.

I understand that .All() isn't supported by Linq to SQL, but is there a way to do what I am trying to do?

Answer 1

Could you do a join OwnerIds?

Answer 2

I don't know of a way to do it with Linq to SQL. The problem is that you need to get your list over to the server so that it can query against it. (your list is in memory on your machine, SQL Server needs to do the filtering on the server)

With straight SQL, you could use a regular SELECT statement with the "in()" operator to do that. (don't go over 1,000 items in the "in")

You could insert all of the ID's into a temp table in SQL, and then join to the table (you could use LINQ with this solution, but it requires 2 steps - the insert (assuming you have a "sets" table), and then the joined query (and then a cleanup query to remove your set).

You could LINQ query without the filter condition and then filter on your in-memory set (not recommended if the unfiltered result set could be large).

Answer 3

Error said "Local sequence (means OwnerIds) cannot be used in Linq to SQL implementation of query operators except the Contains() operator." So you can do:

1) load ALL filteredPatches rows from SQL

var loadedData = filteredPatches.Select(i => i).ToList();

2) filter data as simple local sequence

var result = loadedData.Where(i => i.PatchesOwners.All(o => OwnerIds.Contains(o.ID)));

Answer 4

what the compiler says is...

OwnerIds.Contains(someVariable)

is supported and it will be translated as:

WHERE someVariable IN (OwnerId1, OwnerId2, OwnerIdN)

now, we don't have all the informations of you query but if you can reformulate what you're trying to do to use Contains, you'll be ok.

Answer 5

Customers where OrderIds in the child collection are a subset of the IDs in the in-memory collection.

from c in myDC.Customer
where c.Orders.All(o => myList.Contains(o.ID))
select c;

Customers where OrderIds in the in-memory collection are a subset of the IDs in the child collection.

from c in myDC.Customers
where (from o in c.Orders
    where myList.Contains(o.ID)
    group o.ID by o.ID).Distinct().Count() == myList.Count()
select c;

Customers where OrderIds in the in-memory collection are set-equal to the IDs in the child collection.

from c in myDC.Customers
let Ids = c.Orders.Select(o => o.ID).Distinct()
where Ids.Count() == myList.Count()
  && Ids.All(id => myList.Contains(id))
select c;

All of these generated sql for me.

PS - these presume the IDs are already distinct in myList. If they aren't yet, use:

myList = myList.Distinct().ToList();

PSS - good for lists up to ~2000 items. Higher than that will get translated to sql, and then sql server will barf at the number of parameters.