Optimal Count() operation from a DB using LINQ to SQL

c#, linq-to-sql, performance, t-sql

Solution

As I understand it there's no difference between your two `select count` statements.

Using LINQPad we can examine the T-SQL generated by different LINQ statements.

For Linq to SQL both

TableName.Select(primaryKeyId => primaryKeyId).Count();

and

TableName.Count();

generate the same SQL

SELECT COUNT(*) AS [value] FROM [dbo].[TableName] AS [t0]

For Linq to Entites, again they both generate the same SQL, but now it's

SELECT 
[GroupBy1].[A1] AS [C1]
FROM ( SELECT 
    COUNT(1) AS [A1]
    FROM [dbo].[TableName] AS [Extent1]
)  AS [GroupBy1]

Problem

DBAs have told me that when using T-SQL: ``` select count(id) from tableName ``` is faster than ``` select count(*) from tablenName ``` if id is the primary key. Extrapolating that to LINQ-TO-SQL is the following accurate? This LINQ-to-SQL statement: ``` int count = dataContext.TableName.Select(primaryKeyId => primaryKeyId).Count(); ``` is more performant than this one: ``` int count = dataContext.TableName.Count(); ```

Original source