EF core Access list through Include

Question

I have a Model named as Blog:

public class Blog
    {
        [Key]
        public int blogId { get; set; }
        [Required]
        public string blogTitle { get; set; }
        public string imagePath { get; set; }
        [Required, DataType(DataType.Html)]
        public string blogDescription { get; set; }
        [DataType(DataType.DateTime)]
        public DateTime blogDateTime { get; set; }
        [Required]
        public bool isPublished { get; set; }

        [Required]
        public BlogCategory blogCategory { get; set; }
        public List<Comments> blogComments { get; set; }
    }

And another model Comment:

public class Comments
    {
        [Key]
        public int authorId { get; set; }
        [Required]
        public string commentAuthor { get; set; }
        [DataType(DataType.Date)]
        public DateTime commentDate { get; set; }
        public bool isHidden { get; set; }
        [Required, DataType(DataType.EmailAddress)]
        public string commentAuthorEmail { get; set; }
        [Required]
        public string commentDescription { get; set; }
        public Blog Blog { get; set; }
    }

In the blogs controller I want to access all the comments where isHidden = false

What I have tried is:

var blog = await _context.Blog
           .Include(cat => cat.blogCategory)
           .Include(comments => comments.blogComments.Any(c => !c.isHidden))
           .FirstOrDefaultAsync(m => m.blogId == id);

But all I am getting exception is

An unhandled exception occurred while processing the request.
InvalidOperationException: The Include property lambda expression 'comments => comments.blogComments.Find(c => Not(c.isHidden))' is invalid. The expression should represent a property access: 't => t.MyProperty'. To target navigations declared on derived types, specify an explicitly typed lambda parameter of the target type, E.g. '(Derived d) => d.MyProperty'.

How can I solve this problem?

Unfortunately, it is not possible to filter when using include (yet). See [here](https://github.com/aspnet/EntityFrameworkCore/issues/1833) for details. An alternative is to use global filters, but it might have other repercussions in your app. — ESG, May 27 '19 at 18:04
Possible duplicate of [EF: Include with where clause](https://stackoverflow.com/questions/16798796/ef-include-with-where-clause) — Gert Arnold, May 27 '19 at 19:51
@GertArnold I don't think that this is a possible duplicate. I would like to request you to look over this question again. If possible, Please help. — Shunjid Rahman, May 28 '19 at 05:07
Not a duplicate? What else are you asking than how to do filtered Include? — Gert Arnold, May 28 '19 at 07:37

score 1 · Accepted Answer · answered May 28 '19 at 10:06

You could not do filter in Include in EF core, try to use below code instead

var blog = await _context.Blog
       .Include(cat => cat.blogCategory)
       .Include(comments => comments.blogComments)
       .FirstOrDefaultAsync(m => m.blogId == id);

blog.blogComments = blog.blogComments.Where(b => b.isHidden == false).ToList();

Refer to Filtering on Include in EF Core

score 0 · Answer 2 · answered May 27 '19 at 18:32

0

var blog = await _context.Blog
       .Include(cat => cat.blogCategory)
       .Include(comments => comments.blogComments)
       .Select(x=> {
            x.blogCategory = x.blogCategory;
            x.blogComments = x.blogComments.Where(y=>!y.IsHidden)
       })
       .FirstOrDefaultAsync(m => m.blogId == id);

Since it's a IQueryable this will have the same result. It will only return the comments where the blog comments are hidden. This is considering that you want to return all the blogs and not just the blogs whose comments are hidden.

answered May 27 '19 at 18:32

A_kat

1,459
10
17

This doesn't return a `Blog` though. – Gert Arnold May 27 '19 at 19:52
The trick here is tje select statement. You can return anything you want with it. For example new Blog(){ mpla mpla} – A_kat May 28 '19 at 11:48

score 0 · Answer 3 · answered May 28 '19 at 12:59

0

You must use entity framework projections.

var blog = await _context.Blog
.Select(p => new Blog
{
    blogComments  = p.blogComments.Where(s => !s.isHidden )
}.FirstOrDefaultAsync(m => m.blogId == id);

answered May 28 '19 at 12:59

Ahmet Arslan

5,380
2
33
35

"must" is a bit strong – Robert Perry May 28 '19 at 13:02
afaik there is no other way. look please. https://stackoverflow.com/questions/43618096/filtering-on-include-in-ef-core – Ahmet Arslan May 28 '19 at 13:04

score -1 · Answer 4 · answered May 27 '19 at 18:01

-1

bool value is true of false so try: //untested

(comments => comments.blogComments.where(c=>c.isHidden==false))

answered May 27 '19 at 18:01

Assaf Our

611
3
9
25

EF core Access list through Include

4 Answers4