Linq: doing a Where() clause on a nested model

Question

This may look complicated, but I am really just trying to select all records and their children from A where certain conditions exist in their children. Looking for the syntax I can add to the A variable that allows me to filter a bunch of conditions (like a specification pattern?)

If you have a nested view like this:

var a = from Arow in Atable
        where ???
        select new AViewModel {                    // (image Products)
            id = Arow.id,
            name = Arow.Name,
            Brows = (from Brow in Arow.Brows
                     select new BViewModel {       // (sold in different regions)
                         id = Brow.id,
                         name = Brow.Name,
                         Crows = (from Crow in Brow.Crows
                                  select new CViewModel { // (and in many stores)
                                      id = Crow.id,
                                      name = Crow.Name
                                  }
                     }
        };

And text representing queries from a web page like this (Specification Pattern?) We used JQuery selectors for the operators (like ^= means "starts with")

filter[] = { "Brow.Name = Joe", "Crow.Name = Kim" }

How could you filter the expression A with those criteria? In other words, can you have a where expression like a.Where() that can filter nested properties?

My poor attempt:

var b = from row in a
        where a.Brow.Where(b => b.name == "Joe") &&
              a.Brow.Crow.Where(c => c.name == "Kim")
        select row;

What I really need is something like this:

Select * 
from A join B on A.key = B.key join C on B.key = C.key -- propagated keys
where exists (select null from B where A.key = B.key and B.Name = "Joe") and
      exists (select null from C where A.key = C.key and C.Name = "Kim")

Especially if I can do this:

var result = a.Where(first).Where(second);

Answer 1

Your "poor attempt" isn't so far off. Just substitute Any() for Where():

var b = from row in a
        where a.Brow.Any(b => b.name == "Joe") &&
              a.Brow.Crow.Any(c => c.name == "Kim")
        select row;

Edited to add:

You might want to do a case-insensitive comparison, though, e.g., .Any(b => b.name.Equals("Joe", StringComparison.CurrentCultureIgnoreCase)).