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.

More posts tagged LINQ