0

I have this query witch is 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 the problem is that it is loading all data to my application (i have now 300 000 rows already on my largest table) and its getting slow day by day of course because its loading all in the ToList() method call.

I have this splitted in two calls because i cannot figure out how to make a single call to the database and return only the dataInput, if i merge both calls into one i get "Object reference not set to an instance of an object." exeption, this is probably because some references are not being included, but i cannot figure out what more tables i include in the query...

also im using entity framework 7 which still doesn't support lazy loading and has some features missing still, but this should be possible right? In the includes i tried using a 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 is not recognized (because its new entity framework 7 ?)

Update : Ok if i use the .AsEnumerable i can make all in a single query, but still it seems to take for about 6 seconds to load the data in the next call and also loads 250 mb worth of memory at this 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();
João Ladeira
  • 23
  • 7
  • Did you try a single call *without* the `ToList()`? – Gert Arnold Jun 13 '15 at 20:37
  • If i dont call the ToList() then it does not throws an exception at that time but it will give that when i try to access the data on the next operation, in the debugger i get also before next operation "Children cannot be evaluated" and then in the next operaion it throws the same exception :/ – João Ladeira Jun 13 '15 at 21:22
  • Hmmm that's kinda weird, because if you have one statement the *whole* statement should run as SQL and the CLR shouldn't be aware of any null references whatsoever. This looks like a bug in EF's SQL generation process. By the way, the `Include`s are ignored here because you're creating a projection. – Gert Arnold Jun 13 '15 at 21:30
  • Yea, i had also that idea that one query should be equal to both without the ToList in the middle... but i was thinking that maybe it had something with me not including all the other necessary tables... on entity 7 i know that lazy loading is disabled on this prerelease , maybe i should wait for the final release or ask in their forums then. Thanks Gert. – João Ladeira Jun 13 '15 at 21:36
  • also when i say next operation is for example when i try to do this : var allCities = pivotInput .SelectMany(x => x.VisitationsByCity.Select(c => c.City)) .Distinct().OrderBy(city => city.Name); and foreach each city then throws exception again ... – João Ladeira Jun 13 '15 at 21:38
  • Well, there you go. The `Incude`s are ignored, but that's a different and rather confusing topic altogether, of which I'm not sure if anything will change in EF7. See this: http://stackoverflow.com/a/29308569/861716 or this: http://stackoverflow.com/a/28065280/861716 – Gert Arnold Jun 13 '15 at 21:44
  • Thanks Gert, i will try to post this on their forums to see if this has any solution. Thanks for you help. – João Ladeira Jun 14 '15 at 12:11

1 Answers1

0

Ok so i managed (of sort :P) to put all in a single query but i still have to call the .ToList() in order for the next data retrievals to work, if someone has a 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();
João Ladeira
  • 23
  • 7