LINQ Query - How to map a result set to another object using Select

Question

I have a hierarchy of objects organized as Continents> Countries> Cities. You can select all cities in a specific "country" as shown below. What I'm looking for is a way to combine these two requests and come to cityList in one request.

var cities = network.Continents .SelectMany(continent => continent.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>(); 

"c in cities" has a different structure from the one in cityList, and therefore, the display in the second request.

Answer 1

Just use dot notation in the 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 is readable and has no additional overhead, the generated SQL query is similar, in most cases you can do it, so ease of reading is important.

Answer 2

You should be able to do 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(); 

As an alternative:

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

Answer 3

Another alternative to published options:

 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. Please note: if you want to write it as a query expression, you can do it 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 dotted notation form in this particular case, because the continent and country variables will propagate to the select clause ... but I would expect that the SQL generated by any LINQ database provider should be the same thing, and even inside LINQ to Objects, the difference is likely to be negligible.

Note that you do not need to specify a type argument when calling ToList - the type will be inferred as City already.

Answer 4

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