c# - Query to entity framework 7 using linq and related entities -

i have query witch working fine :

list<igrouping<country, visitedcity>> tempquery = null;         using (var db = new mydatacontext())         {             tempquery = db.visitedcities.include(c => c.personwhovisited).include(c => c.personwhovisitednationality).include(c => c.city)                 .groupby(c => c.personwhovisitednationality)                 .tolist();         }          var datainput = tempquery             .orderby(c => c.key.name)             .select(cp => new             {                 countryname = cp.key.name,                 visitationsbycity = cp                 .groupby(x => x.city, x => x.cityid)                 .select(c => new                 {                     city = c.key,                     numberofvisits = c.count()                 })             }).tolist(); 

but problem is loading data application (i have 300 000 rows on largest table) , getting slow day day of course because loading in tolist() method call.

i have splitted in 2 calls because cannot figure out how make single call database , return datainput, if merge both calls 1 "object reference not set instance of object." exeption, because references not being included, cannot figure out more tables include in query...

also im using entity framework 7 still doesn't support lazy loading , has features missing still, should possible right? in includes tried using select statement

.include(c => c.ladiesinwaiting.select(b => b.princess)) 

like mentioned on here : http://blogs.msdn.com/b/adonet/archive/2011/01/31/using-dbcontext-in-ef-feature-ctp5-part-6-loading-related-entities.aspx

but not recognized (because new entity framework 7 ?)

update : ok if use .asenumerable can make in single query, still seems take 6 seconds load data in next call , loads 250 mb worth of memory @ instance of time ...

var tempquery = db.visitedcities.include(c => c.personwhovisitednationality).include(c => c.city)                 .groupby(c => c.personwhovisitednationality)                 .asenumerable()                 .orderby(c => c.key.name)                 .select(cp => new                 {                     countryname = cp.key.name,                     visitationsbycity = cp                     .groupby(x => x.city, x => x.cityid)                     .select(c => new                     {                         city = c.key,                         numberofvisits = c.count()                     })                 });              var allcities1 = tempquery             .selectmany(x => x.visitationsbycity.select(c => c.city))             .distinct().orderby(city => city.name).tolist(); 

ok managed (of sort :p) put in single query still have call .tolist() in order next data retrievals work, if has better solution please let me know .

var datainput = db.visitedcities                 .groupby(c => c.personwhovisitednationalityid)                 .join(db.countries, s => s.key, c => c.countryid, (s, c) => new { s, c })                 .orderby(c => c.c.name)                 .select(cp => new                 {                     country = cp.c,                     visitationsbycity = cp.s                     .groupby(x => x.cityid)                     .join(db.cities, s => s.key, c => c.cityid, (s, c) => new { s, c })                     .select(c => new                     {                         city = c.c,                         numberofvisits = c.s.count()                     })                 }).tolist();