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.

Answer 2

I'm not sure if it's a hard and fast rule, but linq method you add to an Iqueryable will be added into the linq expression tree - unless they are one of the methods that actually cause the tree to be evaluated (like ToList and Single etc). In the case of LinqToSql you'll know if it can't convert something into the SQL statement because you'll get a runtime exception stating that the method is not supported.

eg

var something = dbContext.SomeTable
  .Select(c => c.col1 == "foo")
  .Distinct()
  .ToList()
  .Count()

In the above, Select() and Distinct() are included in the sql query passed to the server because they are added to an Iqueryable. Count() is just acting on the list that was returned by the sql query. So you don't want to do it that way :-)

In your case, Count() will definitely be faster that Select() because the resulting sql statement will indeed incorporate the count so the server only needs to return a single number rather than a list of rows.