Query to entity framework 7 using linq and related entities

Question

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

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
– João Ladeira, Commented Jun 13, 2015 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 Includes are ignored here because you're creating a projection. — Gert Arnold
– Gert Arnold, Commented Jun 13, 2015 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
– João Ladeira, Commented Jun 13, 2015 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
– João Ladeira, Commented Jun 13, 2015 at 21:38

João Ladeira · Accepted Answer · 2015-06-14 21:34:33Z

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

Collectives™ on Stack Overflow

Query to entity framework 7 using linq and related entities

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related