Display data by Id and the associated many-to-many table data efficiently in ASP.NET Core MVC

Question

My website have many posts and the detail page of each post show the associated information along with the Tag(s) [many-to-many data] the post is tagged with.

Code:

public async Task<IActionResult> Detail(int id)
{
    var Post = await _postInterface.GetByIdAsync(id);

    var Tag = await _context.Posts.Include(p => p.PostTags)
                        .ThenInclude(t => t.Tag).ToListAsync();

    return View(Post);
}

• var Post displays database data according to the id selected.
• var Tag is initialized from the many-to-many relationship database tables.

My question:

Is there a way to initialize only the content without using var Tag = here as it is not required?

await _context.Posts.Include(p => p.PostTags).ThenInclude(t => t.Tag).ToListAsync();

Answer 1

To explain what you are seeing happening, a break-down of your code:

var Post = await _postInterface.GetByIdAsync(id);

This line is fetching a single post from whatever the _postInterface class is. This is commonly called a Repository Pattern and often has a shortcoming that it doesn't easily facilitate things like eager loading related data that callers might want.

var Tag = await _context.Posts.Include(p => p.PostTags)
                    .ThenInclude(t => t.Tag).ToListAsync();

This line isn't actually loading a tag, or tags, it is actually loading ALL Posts, with ALL included tags into the "Tag" variable and the DbContext's tracking cache. (very bad!!!)

The reason this appears to work is because after calling that "read everything" the DbContext is now tracking all posts and tags, so when you go to return the "Post" that you loaded, the proxy references to it's PostTags/Tag can be filled by the DbContext when the consuming code tries to access them. If you remove that var Tags = .... then the Context isn't tracking them and you get #null, or if lazy loading was enabled, it would fetch them when accessed so long as the DbContext wasn't disposed.

What you really want is:

public async Task<IActionResult> Detail(int id)
{
    var post = await _context.Posts
        .Include(p => p.PostTags)
            .ThenInclude(t => t.Tag)
        .AsNoTracking()
        .SingleAsync(p => p.Id == id);
    return View(post);
}

This tells EF we want the one post, but to include that post's postTags and Tags. The AsNoTracking() call is optional, but makes it a tad faster in this case as it tells EF that the DbContext does not need to track these entities. If you are returning detached entities then the Context doesn't need to track the entities, as the DbContext should be disposed anyways at the end of the request. For instance if you happen to add the .AsNoTracking() to your original var Tags = .... query, you'd actually find that your code stops working because it is the tracking cache behaviour that your code is relying on to populate the references when you return "Post".