LINQ Query - How to map a resultset into another object using Select

I have an object hierarchy arranged as Continents > Countries > Cities. Am able to select all the cities in a specific "country" as below. What I am looking for is a way to merge these two queries, and arrive at the cityList in a single query.

var cities = network.Continents
    .SelectMany(continent => co开发者_C百科ntinent.Countries)
    .Where(ctry => ctry.Id == "country")
    .SelectMany(ctry => ctry.Cities);

List<City> cityList = (from c in cities
                        select new City { Id = c.Id, Name = c.Name }).ToList<City>();

The "c in cities" have a different structure from the one in cityList and hence the mapping in the second query.

Just use dot notation in your query:

var cities = network.Continents
    .SelectMany(continent => continent.Countries)
    .Where(ctry => ctry.Id == "country")
    .SelectMany(ctry => ctry.Cities)
    .Select(cty=> new City{Id = cty.Id, Name = cty.Name }).ToList<City>();

I think it's readable, and it doesn't have extra overhead; usually, the generated SQL query is similar to what you may write on your own, hence the readability of this one is its advantage.

You should be able to do just this:

var cityList = network.Continents
    .SelectMany(continent => continent.Countries)
    .Where(ctry => ctry.Id == "country")
    .SelectMany(ctry =>
        ctry.Cities.Select(c => new City { Id = c.Id, Name = c.Name })
    ).ToList();

Alternatively:

var cityList =
    (from continent in network.Continents
     from country in continent.Countries
     where country.Id == "country"
     from city in country.Cities
     select new City { Id = city.Id, Name = city.Name })
    .ToList();

Another alternative to the options posted:

var cityList = network.Continents
                      .SelectMany(continent => continent.Countries)
                      .Where(ctry => ctry.Id == "country")
                      .SelectMany(ctry =>  ctry.Cities,
                                  c => new City { Id = c.Id, Name = c.Name })
                      .ToList();

This overload of SelectMany (in the second call) is the one used by the C# compiler in query expressions. Note that if you want to write it as a query expression, you can do so easily:

var cityList = (from continent in network.Continents
                from country in continent.Countries
                where country.Id == "country"
                from city in country.Cities
                select new City { Id = city.Id, Name = city.Name }).ToList(); 

In LINQ to Objects the query expression will be slightly less efficient than the dot notation form in this particular case, because the continent and country range variables will be propagated down to the select clause... but I'd expect the SQL generated by any database LINQ provider to be the same, and even within LINQ to Objects the difference is likely to be negligible.

Note that you don't need to specify the type argument when calling ToList - the type will be inferred as City already.

Try the following:

var cities = 
    from continent in network.Continents
    from country in continent.Countries
    from city in country.Cities
    where country.Id = "country"
    select new City { Id = c.Id, Name = c.Name };