CODEWITHSUNDEEP
c#entity-frameworklinq
chuckd
chuckd

Reputation: 14540

Entity Framework linq query for multiple children

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
            }
        }

Upvotes: 1

Views: 75

Answers (2)

ocuenca
ocuenca

Reputation: 39326

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

Upvotes: 2

SomeGuy1234
SomeGuy1234

Reputation: 11

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

Upvotes: 1

Related Questions