Growing with the Web

Use .Any() in your LINQ to SQL queries

Published
Tags:

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 Any() over 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() uses 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.

Comments

comments powered by Disqus
Like this article?
Subscribe for more!