Does Linq2Entites Count () with a condition on bool not work like "I thought it would be"?

Question

Given the following very simple linq statement

vm.VerifiedGroups = db.ReportGroups.Count(g => g.Verified); 

or

 vm.VerifiedGroups = db.ReportGroups.Count(g => g.Verified == true); 

where is Verified bool, do I get an exception saying this is not supported by linq-2 entities?

Missed something very simple - or should I choose one of them:

 a) vm.VerifiedGroups = db.ReportGroups.Where(g => g.Verified).Count(); 

or

 b) vm.VerifiedGroups = db.ReportGroups.ToList().Count(g => g.Verified); 

both of these work (and my list is longer than 30-50, so ToList is not a problem).

Answer 1

You have not missed anything. The predicate counter is not supported by Linq to Entity. See the msdn article. Supported and unsupported LINQ methods (LINQ to Entities)

And yes, you have to go with the first parameter, because ToList() will execute the request and print all the objects into memory:

 vm.VerifiedGroups = db.ReportGroups.Where(g => g.Verified).Count(); 

Even if you do not have entries in the ReportGroups table - why do you need to do something that is slower and uses more resources for PC, database and network? Compare the transfer of one integer value to the transfer of all fields from 50 ReportGroup entities, creating .net objects from the DataReader (and keeping the connection open) and iterating over the created list, executing the predicate method for each of the DataReader objects. I think that passing a single integer value wins here.

Answer 2

I would choose the first option, since the first does not bring all the records for calculating the score. In the second variant, the ToList method will output the data and apply the score to it.

 vm.VerifiedGroups = db.ReportGroups.Where(g => g.Verified).Count();