Selecting all child objects in Linq

Question

This really should be easy, but I just can't work it out myself, the interface is not intuitive enough... :(

Let's say I have a State table, and I want to select all Counties from multiple States. In SQL that would be:

select c.*
  from State s join County c on c.StateCode = s.StateCode
 where s.TimeZone = -5 -- or some other criteria

The above example is trivial enough to convert to Linq in a static context:

var q = MyDataContext.GetTable<County>().Where(c => c.State.TimeZone = -5);

But where it starts getting complicated is if I want a more context sensitive query, such as the following:

public static List<County> GetCountiesForStates(List<State> states) {
  // gotta do something to return all the counties for all these states
}

Now I could do something like this inside that method:

var q = MyDataContext.GetTable<County>().Where(c => states.Contains(c.State));

but IMO that is really inelegant, because (a) I have to get a static MyDataContext instead of using the implicit data context of the State objects and (b) you're working backwards, and if you start complicating the query further it gets even uglier.

Is there a way of starting the query with:

var q = states... // or "from s in states..."

Instinctively, I want to believe you can do this, but I haven't yet found the way...

Answer 1

You can do this:

var q = from c in countries
        from s in c.States
        where c.Property == Something
        select s;

This will give you an enumeration of all states within all countries. This translates into the following:

var q = countries.Where(x => c.Property == Something).SelectMany(c => c.States);