Linq to SQL problem

Question

I have a local set of Id entries (integers).

I need to get records that have all the identifiers of their child records in this local collection.

Here is my request:

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 get this error:

The local sequence cannot be used in the implementation of Linq to SQL query operators, except for the Contains () operator.

I understand that .All () is not supported by Linq for SQL, but is there a way to do what I'm trying to do?

Answer 1

Clients where OrderIds in the child collection are a subset of the identifiers in the collection in memory.

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

Clients where OrderIds in the in-memory collection is a subset of the identifiers 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; 

Clients where OrderIds in the in-memory collection are set equal to identifiers 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 these generated sql for me.

PS - they assume that the identifiers are already allocated in myList. If they are not already installed, use:

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

PSS - Suitable for lists of up to ~ 2000 items. Higher than this will be translated into sql, and then the sql server will be barf according to the number of parameters.

Answer 2

I do not know how to do this with Linq to SQL. The problem is that you need to get your list on the server so that it can request it. (your list is in memory on your computer, SQL Server should perform filtering on the server)

With direct SQL, you can use the regular SELECT statement with the "in ()" statement for this. (do not go over 1000 elements in "in")

You can insert the entire ID into a temporary table in SQL, and then join the table (you can use LINQ with this solution, but it takes 2 steps to do this - insert (if you have a "sets" table) and then join the query (and then a cleanup request to delete your set).

You can query LINQ without a filter clause and then filter your set in memory (not recommended if the unfiltered result set can be large).

Answer 3

what the compiler says ...

 OwnerIds.Contains(someVariable) 

supported and it will be translated as:

 WHERE someVariable IN (OwnerId1, OwnerId2, OwnerIdN) 

Now we do not have all the information about your request, but if you can reformulate what you are trying to use to use Contains , you will be fine.

Answer 4

Could you join OwnerIds?

Answer 5

Error: "The local sequence (means OwnerIds) cannot be used in the implementation of Linq to SQL query statements, except for the Contains () statement. So, you can do:

1) load ALL filterPatches rows from SQL

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

2) filter data as a simple local sequence

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