Entity Framework linq query for multiple children

Question

I have this query below but I'm not sure how to write the query so that I don't have to loop through each yogaSpace and run an individual query.

I'd like to run it all at once in one query. FYI - yogaprofile has a one to many with yogaspaces. yogaspaces has a one to many with yogaspaceevents. And I want all the yogaspaceevents from all the yogaspaces from a single yogaprofile.

using (var dbContext = new YogabandyContext())
        {
            var yogaProfile = dbContext.YogaProfiles.Where(i => i.ApplicationUserGuid == userId).First();
            var yogaSpaces = yogaProfile.YogaSpaces;
            var today = DateTime.Now.Date;
            foreach (var yogaSpace in yogaSpaces)
            {
                var yogaEvents = yogaSpace.YogaSpaceEvents.Where(k => k.EventDateTime.Date > today.AddDays(-30) && k.EventDateTime < today.AddDays(30));
                // do something with the yogaEvents here
            }
        }

Possible duplicate of [How to include a child object's child object in Entity Framework 5](http://stackoverflow.com/questions/13047845/how-to-include-a-child-objects-child-object-in-entity-framework-5) — , Feb 13 '17 at 19:30

score 2 · Accepted Answer · answered Feb 13 '17 at 19:44

Start your query from YogaSpaces DbSet. This way you can get the result you are expecting from one query executed in the server side:

//Do this outside of your query, a method call can't be translated to sql
var up= DateTime.Now.Date.AddDays(30);
var down= DateTime.Now.Date.AddDays(-30);

var query= dbContext.YogaSpaces
                    .Where(i => i.YogaProfile.ApplicationUserGuid == userId)
                    .SelectMany(i=>i.YogaSpaceEvents.Where(k => k.EventDateTime.Date > down && k.EventDateTime < up));

score 1 · Answer 2 · answered Feb 13 '17 at 19:38

1

Is this what you are looking for?

yogaspaceevents = yogaSpaces.SelectMany(s=>s.YogaSpaceEvents.Where(k
=> k.EventDateTime.Date > today.AddDays(-30) && k.EventDateTime < today.AddDays(30)))

answered Feb 13 '17 at 19:38

SomeGuy1234

11
1

Entity Framework linq query for multiple children

2 Answers2