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...

eulerfx · Accepted Answer · 2009-06-17 20:54:06Z

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

This answer is somewhat confusing, because it references "countries" while the OP is concerned with "counties". The hierarchy is therefore reversed.

Collectives™ on Stack Overflow

Selecting all child objects in Linq

1 Answer 1

2 Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Related