count VS select in LINQ - which is faster?

Question

I'm using IQueryable<T> interfaces throughout my application and defer execution of SQL on the DB until methods like .ToList()

I will need to find the Count of certain lists sometimes -without- needing to use the data in the list being counted. I know from my SQL experience that a SQL COUNT() is far less work for the DB than the equivalent SELECT statement that returns all the rows.

So my question is: will it be less work on the DB to return the count from the IQueryable<T>'s Count() method than rendering the IQueryable<T> to a list and invoking the list's Count() method?

I suspect it will given that the ToList() will fire the SELECT sql and then in a separate query count the rows. I'm hoping the Count() on the IQueryable<T> simply renders out the sql for a sql COUNT() query instead. But im not certain. Do you know?

Answer 1

Calling ToList() will return a genuine List<T> with all the data, which means fetching all the data. Not good.

Calling Count() should indeed render the SQL to do the count on the database side. Much better.

The simplest way to check this, however, is to enable logging in your data context (or whatever the equivalent is for your particular provider) and see what queries are actually being sent.