EF nested tables eager loading?

Question

My table relations:

Categories has many Posts has many PostImages

I want to retrieve Categories>LastPost>FirstPostImage. And I tried something like following:

var categories = entity.Categories
                 .Where(x => x.PositionId == 2)
                 .Include(x => x.Posts.Last())
                 .Include(x => x.Posts.Last().PostImages.First())
                 .Take(5)
                 .ToList();

Question: Is there an elegant way to achive this? Is there commonly usage of eager loading for nested relational tables?

score 2 · Accepted Answer · answered Mar 14 '13 at 10:41

EF doens't support queries on included tables, but you can use a Select clause for sub-queries:

var categories = entity.Categories
  .Where(x => x.PositionId == 2)
  .Select( x => new {
    Category = x,
    LastPost = x.Posts.Last(),
    FirstImage = x.Posts.Last().PostImages.First(),
  })
  .Take(5)
  .ToList();

You will end up with a List<anonymous> but it will get ( just ) the data you need.

score 1 · Answer 2 · edited May 23 '17 at 12:04

I would recommend you to use Data transfer Objects(DTO). Just create new class:

public class CategoriesPostPostImagesDto
{
     public Categories Category {get;set;}
     public Posts Post {get;set;}
     public PostImages PostImage {get;set;}

}

And then select data to it from DB.

List<CategoriesPostPostImagesDto> data = entity.Categories
  .Where(x => x.PositionId == 2)
  .Select( x => new CategoriesPostPostImagesDto(){
    Category = x,
    Post = x.Posts.Last(),
    PostImage = x.Posts.Last().PostImages.First(),
  })
  .Take(5)
  .ToList();

There is a lot of information about the DTO in the internet:

like this: ADO.NET EF - populate DataGridView when we have Foreign Key

EF nested tables eager loading?

2 Answers2