Eager Loading Deeply into a Model with a Collection Property Whose Type is Inherited

Question

Visual Studio generated this great route for me, where I can load an entity:

// GET: api/Jobs/5
[ResponseType(typeof(Job))]
public async Task<IHttpActionResult> GetJob(int id)
{
 Job job = await db.Jobs.FindAsync(id);
 if (job == null)
 {
 return NotFound();
 }
 return Ok(job);
}

Here's the Job model this is based on:

public class Job
{
 public Job()
 {
 this.Regions = new List<Region>();
 this.Files = new List<JobFile>();
 }
 public int ID { get; set; }
 public string Name { get; set; }
 public List<Region> Regions { get; set; }
 public JobTypes JobType { get; set; }
 public int UserIDCreatedBy { get; set; }
 public int? UserIDAssignedTo { get; set; }
 public List<JobFile> Files { get; set; }
 public bool IsLocked { get; set; } // Lock for modification access
}

Here's the JobFile class, which Jobs have a list of:

public class JobFile
{
 public int ID { get; set; }
 public string Name { get; set; }
 public string Url { get; set; }
 public int Job_ID { get; set; }
}

and Pdf, a subclass of JobFile:

public class Pdf : JobFile
{
 public Pdf()
 {
 this.PdfPages = new List<PdfPage>();
 }
 public int Index { get; set; }
 public List<PdfPage> PdfPages { get; set; }
}

Now, when I hit that route, I'd like to eagerly load all the Pdfs for a Job, including their pages. I modified the route to look like this, and it works.

// GET: api/Jobs/5
[ResponseType(typeof(Job))]
public async Task<IHttpActionResult> GetJob(int id)
{
 Job job = await db.Jobs.FindAsync(id);
 // Lookup the PDFs for this job and include their PdfPages
 List<JobFile> jobPdfs = db.Pdfs.Include(pdf => pdf.PdfPages).Where(pdf => pdf.Job_ID == id).ToList<JobFile>();
 // Attach the job files to the job
 job.Files = jobPdfs;
 if (job == null)
 {
 return NotFound();
 }
 return Ok(job);
}

Is this the best way to eagerly load all these models? Could this somehow be collapsed into one statement? It seems right now it hits the database twice. Could I build off of the original

Job job = await db.Jobs.FindAsync(id);

to load the Pdfs and their PdfPages all in one query?

This question provided some helpful insight, but I'm not sure how I can capitalize on its conclusions. I think I need the ToList<JobFile>() (which according to the question does a trip to the database) because I actually need that data. So unless I can squash it into one more complicated Linq statement, perhaps it's unavoidable to make two trips.

Answer 1

The problem here is that you'd actually want to include subtypes. If Job had a collection of Pdfs, you could have done

Job job = await db.Jobs
 .Include(j => j.Pdfs.Select(pdf => pdf.PdfPages))
 .SingleOrDefaultAsync(j => j.Id == id);

But Pdf is a subtype, and EF doesn't support a syntax like

Job job = await db.Jobs
 .Include(j => j.Files.OfType<Pdf>().Select(pdf => pdf.PdfPages))
 .SingleOrDefaultAsync(j => j.Id == id);

So what you do is the only way to get the Job with Pdfs and PdfPages.

There are some improvements to be made though:

• You can just load the child objects into the context without assigning them to job.Files yourself. EF will knit the entities together by relationship fixup.

• You can first check if the Job is found and then load the Pdfs.

Turning it into this:

Job job = await db.Jobs.FindAsync(id);
if (job == null)
{
 return NotFound();
}
else
{
 // Load the PDFs for this job and include their PdfPages
 await db.Pdfs.Include(pdf => pdf.PdfPages).Where(pdf => pdf.Job_ID == id)
 .LoadAsync();
}
return Ok(job);