1
\$\begingroup\$

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.

\$\endgroup\$
1
\$\begingroup\$

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);
\$\endgroup\$

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.