C# ASP.NET Core Web API include with where

Question

I have a code like this:

public Blog GetBlogWithCategoryTagsAndCommentsWithReplies(int id)
    {
        return _context.Blogs
            .Where(blog => blog.Id == id)
            .Include(blog => blog.Category)
            .Include(blog => blog.BlogTags)
                .ThenInclude(blogtag => blogtag.Tag)
            .Include(blog => blog.Comments)
                .ThenInclude(comment => comment.User)
            .Include(blog => blog.Comments)
                .ThenInclude(comment => comment.Replies)
                    .ThenInclude(reply => reply.User)
            .FirstOrDefault();
    }

works without any problem in this state.

But when I add where to my code, when I make it like

public Blog GetBlogWithCategoryTagsAndCommentsWithReplies(int id)
    {
        return _context.Blogs
            .Where(blog => blog.Id == id)
            .Include(blog => blog.Category)
            .Include(blog => blog.BlogTags)
                .ThenInclude(blogtag => blogtag.Tag)
            .Include(blog => blog.Comments.Where(comment=>comment.Confirmation==true))
                .ThenInclude(comment => comment.User)
            .Include(blog => blog.Comments)
                .ThenInclude(comment => comment.Replies.Where(reply=>reply.Confirmation==true))
                    .ThenInclude(reply => reply.User)
            .FirstOrDefault();
    }

so when I ask comments and replies to return confirmation == true, I get the following error.

Error : Lambda expression used inside Include is not valid.

How do I fix this problem?

Answer 1

I'm afraid filtered include is only supported from EF Core v5.0.0, which is in preview at this very moment.

If you're using an older EF Core version, you can check some ideas from this thread.

For instance, if you can afford the performance impact of pulling everything and filtering in memory, you can try to do something like:

var blog = _context.Blogs
   .Where(blog => blog.Id == id)
   .Include(blog => blog.Category)
   .Include(blog => blog.BlogTags)
      .ThenInclude(blogtag => blogtag.Tag)
   .Include(blog => blog.Comments)
      .ThenInclude(comment => comment.User)
   .Include(blog => blog.Comments)
      .ThenInclude(comment => comment.Replies)
         .ThenInclude(reply => reply.User)
   .FirstOrDefault();

blog.Comments = blog.Comments.Where(comment => comment.Confirmation == true).ToList();

foreach(var comment in blog.Comments)
{
   comment.Replies = comment.Replies.Where(reply => reply.Confirmation == true).ToList();
}

return blog;