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.

score 2 · Accepted Answer · answered Jan 21 '15 at 12:45

2

Here you go,

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

answered Jan 21 '15 at 12:45

fhnaseer

7,159
16
60
112

That looks right, I think mine was trying a little too hard.. lol Recompiling and testing now. – Eric Bishard Jan 21 '15 at 12:51
Booyah! Jeez I even had my question structured in the same way you did here. Keyword.... ANY()!!! – Eric Bishard Jan 21 '15 at 12:51
1

Linq is best. Make it simple, – fhnaseer Jan 21 '15 at 12:54

score 1 · Answer 2 · answered Jan 21 '15 at 12:06

1

I think this may work:

var commented = _db.Renders.Include("Comments")
               .Where(c => c.CommentApproved)
               .Count());

The Select is not necessary here I guess

answered Jan 21 '15 at 12:06

MakePeaceGreatAgain

35,491
6
60
111

This solution gives me a compiler error: "Ambiguous invocation" it assumes that your lambdas c value is a render. – Eric Bishard Jan 21 '15 at 12:35
This is very similar to where I started out: `_db.Renders.Include("Comments").Count(r => r.Comments.Where(c => c.CommentApproved));` which doesn't work but I assumed would be the correct way of doing it. – Eric Bishard Jan 21 '15 at 12:38
Well, you may use `SelectMany` instead of `Include`, hence the latter does no type-checking. – MakePeaceGreatAgain Jan 21 '15 at 12:49

Get Count on Entity with ICollection that has specific property value

2 Answers2