LINQ Query with 3 levels

Question

I have a business object structured like this:

Country has States, State has Cities

So Country[2].States[7].Cities[5].Name would be New York

Ok, I need to get a list of all the Country objects which have at least 1 City.IsNice == true

How do I get that?

Answer 1

var result = (from country in db.Countries
             from state in country.States
             from city in state.Cities
             where city.IsNice
             select county).Distinct();

Answer 2

I would do it in one of two ways:

var selectedCountries = from country in countries
                        from state in country.States
                        from city in state.Cities
                        where city.IsNice
                        select country;

or

var selectedCountries =
    countries.Where(country =>
                    country.States.FirstOrDefault(state =>
                                                  state.Cities.FirstOrDefault(city =>
                                                                              city.IsNice) != null) != null);

Answer 3

var result = Countries
    .SelectMany(a => a.States)
    .SelectMany(b => b.Cities)
    .Where(b => b.IsNice == true)
    .ToList();

Answer 4

var selectedCountries =
    countries.Where(
        co => co.States.Any(
            s => s.Cities.Any(
                ci => ci.IsNice)));

Another option :

var selectedCountries =
    countries.Where(
        co => co.States.SelectMany(s => s.Cities).Any(
            ci => ci.IsNice));