CODEWITHSUNDEEP
c#entity-framework-core
Pascal
Pascal

Reputation: 12855

Need to query related data with ONE query

That is my simple ERD

enter image description here

For a given schoolyearId I want to load all schoolclasses + related pupils + related subjects.

  var schoolclasses = context.Schoolclasses
                .Include(s => s.Pupils)
                // How to get here the SubjectPupil + Subject for each pupil?
                .Where(s => s.SchoolyearId == schoolyearId);

The only special thing in this ERD is the Many to Many relation with Pupil and Subject.

In EF7 I created a bridge table for it like SubjectPupil.

How would you extend my query above or do it correctly to get the Subject data for each pupil in the above query?

Upvotes: 0

Views: 69

Answers (2)

Pascal
Pascal

Reputation: 12855

For all the downvoters and closer idiots without comment.

From this SO answer I found out that Intellisense did not show up for the .ThenInclude() correctly. Thus I thought the query does not work:

EF7 nested Include not showing in Razor .net

The solution to get the data I wanted is this.

  var schoolclasses = await context.Schoolclasses
                .Include(x => x.Pupils)
                .ThenInclude(x => x.PupilsSubjects)
                .ThenInclude(x => x.Subject)
                .Where(s => s.SchoolyearId == schoolyearId)
                .ToListAsync();

Just type the name of your property to ThenInclude and build the project, it will compile!

Upvotes: 1

gerrod
gerrod

Reputation: 6627

Hard to tell without seeing the classes code, but if in essence your question is how you eager load related entiites from multiple levels, you can do something like this:

var schoolclasses = context.Schoolclasses
    .Include(s => s.Pupils.Select(pupil => pupil.SubjectPupil.Subject))
    .Where(s => s.SchoolyearId == schoolyearId);

Here's the MSDN article that describes this behaviour.

Upvotes: 0

Related Questions