LINQ to SQL where the collection contains the collection

Question

I have a problem :( I have a table with many tables between two tables (1 & 2), through a mapping table (3):

(1)Trees / (2)Insects TreeID <- (3)TreeInsects -> InsectID 

And then the one-to-many relationship:

 Trees.ID -> Leaves.TreeID 

And I would like to fulfill a query that will give me all Sheets for a collection of insects (through the display table of insect trees).

eg. I have a List<Insects> , and I want all the Sheets to have a link to any of the Insects in the List through the Tree-Insects mapping table.

This seems like a simple task, but for some reason I am having problems with this!

The best I have: but Single () makes it wrong:

  from l in Leaves where (from i in Insects select i.ID) .Contains((from ti in l.Tree.TreeInsects select ti.InsectID).Single()) select l; 

Answer 1

I am not good at sql-like syntax, so I will write with extensions.

 ctx.Leaves.Where(l => ctx.TreeInsects.Where( ti => list_with_insects.Select(lwi => lwi.InsectID).Contains( ti.InsectID ) ).Any( ti => ti.TreeID == l.TreeID ) ); 

Answer 2

 (from i in insectsCollection select from l in Leaves let treeInsectIDs = l.Tree.TreeInsects.Select(ti => ti.InsectID) where treeInsectIDs.Contains(i.ID) select l) .SelectMany(l => l) .Distinct(); 

Answer 3

Try exploring the SelectMany method - I think it might be the key you need.

I would get a list of the trees available for this Insect, then bind SelectMany to the end and pull out a collection of Lines attached to this Tree.

Answer 4

 List<int> insectIds = localInsects.Select(i => i.ID).ToList(); //note - each leaf is evaluated, so no duplicates. IQueryable<Leaf> query = from leaf in myDataContext.Leaves where leaf.Tree.TreeInsects.Any(ti => insectIds.Contains(ti.InsectId)) select leaf; //note, each matching insect is found, then turned into a collection of leaves. // if two insects have the same leaf, that leaf will be duplicated. IQueryable<Leaf> query2 = from insect in myDataContext.Insects where insectIds.Contains(insect.ID) from ti in insect.TreeInsects from leaf in ti.Tree.Leaves select leaf; 

Also note that Sql Server has a parameter limit of ~ 2100. LinqToSql is happy to generate a query with a large number of insect identifiers, but when you try to start it, you will get a sql exception. To solve this problem, run the query more than once on smaller batches of identifiers.

Answer 5

How do you get this list of insects? Is that a request too?

In any case, if you are not opposed to performance (SelectMany may be slow if you have a large database), this should work:

 List<Insect> insects = .... ; //(your query/method) IEnumerable<Leave> leaves = db.TreeInsects .Where(p=> insects.Contains(p.Insect)) .Select(p=>p.Tree) .SelectMany(p=>p.Leaves);