Why using Count with IQueryable is considered unfeasible

Question

If I have the following code:-

IQueryable<People> list = repository.FindAllPeople;  
int count = list.Count(); 

Then is it considered as unfeasible to count IQueryable objects and it is better to use IEnumerable? BR

Answer 1

You have been misinformed.

IEnumerable will use Linq to objects, all methods are executed on objects in memory. - IQueryable will use whatever implementation of the Linq extension methods is provided by the specific provider. In this case (a repository) I would guess it is most likely a provider that maps the Linq expressions to database statements.

That means if you use IQueryable:

IQueryable<People> list = repository.FindAllPeople;  
int count = list.Count(); 

The count is determined on the database itself, i.e. as a query "select count(*) from People". This is usually very, very fast.

If you use IEnumerable:

IEnumerable<People> list = repository.FindAllPeople;  
int count = list.Count(); 

All People instances will be materialized to memory one by one while Linq to objects is iterating through the collection to determine the count. This will be very slow and should be avoided whenever possible.

Since not all method calls can be mapped to database queries it is sometimes unavoidable to use an IEnumerable, but all filtering, joining and grouping should be done on an IQueryable if possible, then as a last step you can use the AsEnumerable() extension methods to switch to using IEnumerable and Linq to objects.