How do you do a join in LinqToSQL?

Question

I have a data setup of the following form: State 1->n County 1->n City.

Within my State object, I want to return all counties that contain at least one city with a population greater than p. Were I to write this in sql it would be:

select distinct co.*
from County co join City ci on ci.CountyID = co.ID
where ci.Population > @p
and co.StateCode = @StateCode

Maybe the sql could be optimized better (and I'll certainly appreciate pointers there), but that's not the point...

Anyway, I want to do this in Linq, in the State class. My code (obviously not compiling) now looks like this:

var q = 
  from co in Counties
  where co.Cities // uh-oh, now what?

How do you do it?

Answer 1

Assuming you have association properties...

var q =  from co in Counties
         where co.Cities.Any(city =>city.Population > p)
         select co;

Or simply:

var q = Counties.Where(co => co.Cities.Any(city => city.Population > p));