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)

Why the negative votes? Is there something wrong with this question? I thought it would be useful to share my solution with others.... :-/ — Jason Parker, Dec 10 '13 at 19:27
Some people might not know that [its ok to ask and answer your own questions!](http://blog.stackoverflow.com/2011/07/its-ok-to-ask-and-answer-your-own-questions/) — Peter Hansen, Dec 10 '13 at 22:31
@PeterHansen But answering your own questions seconds after you post the question is just strange, just dont ask in the first place. Its better to just delete the question instead of posting it anyway, or if you think "Hey I wasted minutes in typing this question so let post it." than just let someone else give the answer, who cares.. since the time between Question and Answer is this close it seems like he did it on purpose just to get reputation points. — Rand Random, Dec 11 '13 at 14:06
@PeterHansen Thanks for explaining. I was curious what I did wrong.... thought my question might have been too long. — Jason Parker, Dec 11 '13 at 16:17
@RandRandom When creating a question on stackoverflow, there is a checkbox right under the submit button that says "Answer Your Own Question". (That is to say, this mechanism was planned for and built into the stackoverflow framework.) I used this checkbox, in order to submit both question and answer at the same time. The purpose is to save others trouble by supplying the problem and solution together. If I cancelled out this post and just kept to myself (which would have saved me time) then no one gains from my experience. — Jason Parker, Dec 11 '13 at 16:19

score 2 · Answer 1 · answered Dec 10 '13 at 19:08

2

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.

answered Dec 10 '13 at 19:08

Jason Parker

4,960
4
41
52

Ye, it can get kind of including. :> – scheien Dec 10 '13 at 20:19
I mean that it gets a lot of includes if you need to eager load a lot of associated entities. It was however meant as a joke :) – scheien Dec 11 '13 at 07:03
@scheien Oh ok. :) Well yeah it does seem like lots of includes. The reason is because my database is fairly normalized, so I am pulling in lots of associated records joining against tables. (FYI, in my case I am then serializing them into JSON for use on the client side in JavaScript.... so I need to have everything already queried.... the DbContext will be closed out at this point.) – Jason Parker Dec 11 '13 at 16:23
It could be ok to mention that one could also use a string to specify the relationships. Like: ctx.Entity1.Include("Entity2").Include("Entity2.Entity3").ToList(); – scheien Dec 11 '13 at 18:07
Yes that is possible, but by using strings then you miss out on intellisense and compile time error checking. – Jason Parker Dec 11 '13 at 19:48
Yep, but sometimes strings are just a better fit. :-) – scheien Dec 12 '13 at 07:17

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

1 Answers1