LINQ to SQL where collection contains collection

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

(1)Trees / (2开发者_开发知识库)Insects 

TreeID <- (3)TreeInsects -> InsectID   

And then a one to many relationship:

Trees.ID -> Leaves.TreeID

And I would like to perform a query which will give me all the Leaves for a collection of insects (via trees-insect mapping table).

E.g. I have List<Insects> and I want all Leaves that have an association with any of the Insects in the List via the Tree-Insects mapping table.

This seems like a simple task, but for some reason I'm having trouble doing this!!

The best I have: but the Single() makes it incorrect:

   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;

(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();

I'm bad with sql-like syntax so I'll 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 ) );

Try investigating the SelectMany method - I think that may be the key you need.

I would get a list of Trees that are available to that Insect, then peg on a SelectMany to the end and pull out the collection of Leaves tied to that Tree.

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, Sql Server has a parameter limit of ~2100. LinqToSql will happily generate a query with more insect IDs, but you'll get a sql exception when you try to run it. To resolve this, run the query more than once, on smaller batches of IDs.

How do you get this list of insects? Is it a query too?

Anyway, if you don't mind performance (SelectMany can be slow if you have a big 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);