Linq2Entites Count() with condition on bool not working as "I thought it would"?

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 Verified is a bool, I get an exception saying this is not supported by linq-2-entities?

Have a missed something very simple - or should I choose one from:

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

or

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

both these work (and my list is only 30-50 long, so ToList isn't a problem).

Answer 1

You haven't missed anything. Count with predicate is not supported by Linq to Entitities. See msdn article Supported and Unsupported LINQ Methods (LINQ to Entities)

And yes, you should go with first option, because ToList() will execute query and bring all entities into memory:

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

Even if you don't have many records in your ReportGroups table - why would you do something, which is slower, and uses more pc, database and network resources? Compare transferring one integer value to transferring all fields of 50 ReportGroup entities, creating .net objects from DataReader (and holding connection opened), and iterating over created list, executing predicate method on each of DataReader entities. I think transferring one integer value wins here.