EF Eager Load - How to join deeply across several tables?

Question

I have a well-normalized database and am having trouble crafting an EF query to perform a select joining together enough information from several different tables at once.

I realize I could issue separate queries to pull in the associated data, but I'd like to see if it's possible to query everything at once (eagerly).

Data model looks like this:

• Template has one ref_TemplateType, one ref_FormType, and many TemplateMeasurements
• TemplateMeasurement has one Measurement
• Measurement has one ref_MeasurementType, and one Review

I want to pull a list of all templates, including all the associated entities above.

Here is what I started with:

var test = dc.Templates
    .Include(x => x.ref_TemplateType)
    .Include(x => x.ref_FormType)
    .Include(x => x.TemplateMeasurements)
    .ToList();

So far so good.

No I want to include the Measurement entity that belongs to each TemplateMeasurement entity. So I do the following (because TemplateMeasurements is a list):

var test = dc.Templates
    .Include(x => x.ref_TemplateType)
    .Include(x => x.ref_FormType)
    .Include(x => x.TemplateMeasurements.Select(y => y.Measurement))
    .ToList();

PERFECT. Excellent. (Superb.) Now for each TemplateMeasurement, it is joining to pull in the appropriate Measurement record.

But.... that's not good enough. I also want the 'ref_MeasurementType' related entity off of each Measurement object. So I refine my query like so:

var test = dc.Templates
    .Include(x => x.ref_TemplateType)
    .Include(x => x.ref_FormType)
    .Include(x => x.TemplateMeasurements.Select(y => y.Measurement.ref_MeasurementType))
    .ToList();

This works too! (Isn't life wonderful?)

But wait a second........ there is another related entity on each Measurement object that I want, called 'Review'.

How am I supposed to eagerly fetch this one as well??? It won't go into the .Select() method call above... because that method is already being used to pull in 'ref_MeasurementType' entity.

How do I also eagerly fetch the 'Review' entity in this query?

(See below, as I have come up with the solution while I was writing out this question)

Answer 1

Came up with this solution while writing the question. :)

I tested and it generates a single select query against SQL Server, as desired.

var test = dc.Templates
    .Include(x => x.ref_TemplateType)
    .Include(x => x.ref_FormType)
    .Include(x => x.TemplateMeasurements.Select(y => y.Measurement.ref_MeasurementType))
    .Include(x => x.TemplateMeasurements.Select(y => y.Measurement.Review))
    .ToList();

The solution is to simply add ANOTHER .Include() line that traverses down the same 'TemplateMeasurements' collection.... but in the Select() method at the end this time you can specify the other entity you want to eagerly grab.