How do you retrieve records and all child records in one database hit in ADO.NET Entities?

Question

Hi there,

I would like to make one database call to retrieve the following data, but I'm struggling to figure out what the LINQ should look like. Here's my current implementation (I know it's bad!!):

var photos = from photo in entitiesContext.Photo
             join pg in entitiesContext.PhotoGallery on photo.PhotoGallery.PhotoGalleryID equals pg.PhotoGalleryID
             where pg.PhotoGallery == photoGalleryID
             select photo;

var photoList = photos.ToList();

foreach (var photoForLoading in photoList)
{
    photoForLoading.UserReference.Load();
    photoForLoading.PhotoComments.Load();

    foreach (var comment in photoForLoading.PhotoComment)
    {
        comment.UserReference.Load();
    }
}

return photoList;

So as you can see above I want to retrieve:

• All the photos from a specific photo gallery:
  • The user details on each photo
  • The comments on each photo
    • The user details on each comment

How do I do this in LINQ with ADO.NET Entities Framework?

Cheers, Ash.

Answer 1

Maybe change the select to something like:

select new {photo, photo.UserReference, photo.PhotoComments}

Answer 2

Use the .Include method to load the related entities. For example:

from photo in entitiesContext.Photo
                          .Include("UserReference")
                          .Include("PhotoComments")
etc...

Answer 3

When creating your ObjectQuery, call the Include method, passing in the name of the subentity that you want to load automatically. You can chain calls to Include if you have multiple subentities that you want to load.

See this article for more information.