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 — user47589
– user47589, Commented Feb 13, 2017 at 19:30

ocuenca · Accepted Answer · 2017-02-13 19:44:25Z

2

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

answered Feb 13, 2017 at 19:44

ocuenca

39.5k12 gold badges95 silver badges107 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

SomeGuy1234 · Accepted Answer · 2017-02-13 19:38:22Z

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, 2017 at 19:38

SomeGuy1234

111 bronze badge

Collectives™ on Stack Overflow

Entity Framework linq query for multiple children

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related