Get Count on Entity with ICollection that has specific property value

Question

I have a Model called Renders.

public class Render
{
    public int RenderId { get; set; }
    public string ClientName { get; set; }
    public string Title { get; set; }

    public ICollection<Comment> Comments { get; set; }
}

And a collection of Comments in that Render Entity, it's model looks like so:

public class Comment
{
    public int CommentId { get; set; }

    [Column(TypeName = "varchar(Max)"), DataType(DataType.MultilineText)]
    public string UserComment { get; set; }
    public bool CommentApproved { get; set; }

    public int RenderId { get; set; }
    public virtual Render Render { get; set; }

    public virtual ICollection<CommentImage> CommentImages { get; set; }
}

I need to get a Count() of all Renders that have Any Comments with a property CommentApproved that equals true.

This is what I have tried, but it doesn't work because of the Select method and I kind of get why that doesn't work, but it's the only thing that I could figure out how to write that would actually compile.

private readonly RenderLibContext _db = new RenderLibContext();
....
var commented = _db.Renders.Include("Comments")
                   .Select(r => r.Comments.Where(c => c.CommentApproved)
                   .Count());

Obviously there is some other code in my controller, but I wanted to keep the question to the point. I'm just having trouble figuring out how I can get that Count() method to work and tell me how many Renders have Comments in which the ApprovedComment property is true.

Answer 1

Here you go,

var count = _db.Renders.Count(render => render.Comments.Any(c => c.CommentApproved));