This article explains why you should use the
Any() method over
Count() in LINQ to SQL.
When using LINQ to SQL to check whether either no records exist or at least 1 record exists, be sure to prefer
Count() as the SQL will be optimised to only get the information required.
Any() will use
EXISTS in SQL which stops as soon as a record is found whereas
COUNT(*) which goes through all the records to get the number matching the query.
Consider the following code samples:
var q1 = TableName.Count() > 0; var q2 = TableName.Any();
They will produce the following SQL:
-- q1 SELECT COUNT(*) AS [value] FROM [TableName] AS [t0] -- q1 SELECT (CASE WHEN EXISTS( SELECT NULL AS [EMPTY] FROM [TableName] AS [t0] ) THEN 1 ELSE 0 END) AS [value]
As you can see the second sample produces some admittedly uglier SQL but is much more efficient.