CODEWITHSUNDEEP
c#sqllinq
user1027620
user1027620

Reputation: 2785

LINQ Getting count on related entities (many to many)

I need to return the count for the below voters:

Users has many to many relationship with Voters. I would like to return Voters count related to Users. I've tried this: db.Users.Voters.Count() but fails miserably as it does not recognize .Voters.

Is there any way to do this directly?

Upvotes: 0

Views: 192

Answers (3)

Honorable Chow
Honorable Chow

Reputation: 3153

You need to use SelectMany to extract the matches from the many to many.

Users.SelectMany(x => x.Voters.Where(y => y.Users.Any(z => z.Id == x.Id))).Count()

Upvotes: 0

D Stanley
D Stanley

Reputation: 152634

I'm assuming .Voters is a property of a single User object, not a property of the Users collection. In that case one way would be:

db.Users.Sum(user => user.Voters.Count());

but that will double-count any voters that are shared by users. If instead you want a distinct count use

d.Users.SelectMany(user=>user.Voters).Distinct().Count();

Upvotes: 2

terjetyl
terjetyl

Reputation: 9565

Would this work? 

db.Users.Voters != null ? db.Users.Voters.Count() : 0

Upvotes: 1

Related Questions