c# - Entity Framework: Query is slow -

c# - Entity Framework: Query is slow -

i having problems entity framework. have simplified create easier explain.

these mssql tables

i utilize next code cities each of countries in mssql database

var country = new country() { cities = obj.counties.selectmany(e => e.cities).select(city => new dccity { name = city.name, population = city.population }) };

this returned json

there bit more 40.000 records in city table. retrieve list countries , respective cities takes around 8 seconds. trying cut down this. know optimization tips accomplish this?

you need query cities table first data:

var cities = _context.cities.select(x => new { contryid = x.county.country.countryid, contryname = x.county.country.name, cityid = x.id, cityname = x.name }); var countrylookup = new dictionary<int, countrydto>(approximatelycountofcountries); foreach (var city in cities) { countrydto country; if (!countrylookup.trygetvalue(city.countryid, out country)) { country = new countrydto { name = city.countryname, id = city.countryid cities = new list<citydto>(approximatelycountofcities) }; countrylookup.add(country.id, country); } country.cities.add(new citydto { name = city.name, id = city.id }); }

in way result the:

countrylookup.values

c# sql-server linq entity-framework optimization