CODEWITHSUNDEEP
c#performancet-sqllinq-to-sql
Guy
Guy

Reputation: 67300

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

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();

Upvotes: 4

Views: 532

Answers (2)

Gavin
Gavin

Reputation: 516

I know this is an old one but watch out with sql server! Count does not count null values so the two statements may not be equivalent if your primary key field is nullable. see

create table #a(col int null)

insert into #a values (null)

select COUNT(*) 
from #a;

select COUNT(col)
from #a;

Upvotes: 0

Phil
Phil

Reputation: 42991

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]

Upvotes: 6

Related Questions