What is the fastest way to determine if a row exists using Linq to SQL?

I am not interested in the contents of a row, I just want to know if a row exists. The Name column is a primary key, so there will either be 0 or 1 matching rows. Currently, I am using:

if ((from u in dc.Users where u.Name == name select u).Count() > 0)
    // row exists
else
    // row doesn't exist

While the above works, it does a lot of unnecessary work by selecting all the contents of the row (if it exists). Does the following create a faster query:

if (dc.Users.Where(u => u.Name == name).Any())

...or is there an even faster query?

Solution 1:

The Count() approach may do extra work, as (in TSQL) EXISTS or TOP 1 are often much quicker; the db can optimise "is there at least one row". Personally, I would use the any/predicate overload:

if (dc.Users.Any(u => u.Name == name)) {...}

Of course, you can compare what each one does by watching the TSQL:

dc.Log = Console.Out;

Solution 2:

Of Course

if (dc.Users.Where(u => u.Name == name).Any())

this is best and if multiple conditions to check then it is very simple to write as

Say you want to check the user for company then

if (dc.Users.Where(u => u.ID== Id && u.Company==company).Any())

Solution 3:

I think:

if (dc.Users.Any(u => u.Name == name)) {...}

is the best approach.

Solution 4:

For those people claiming Any() is the way forward I've done a simple test in LinqPad against a SQL database of CommonPasswords, 14 million give or take. Code:

var password = "qwertyuiop123";

var startTime = DateTime.Now;
"From DB:".Dump();
startTime = DateTime.Now;

if (CommonPasswords.Any(c => System.Data.Linq.SqlClient.SqlMethods.Like(c.Word, password)))
{
    $"FOUND: processing time: {(DateTime.Now - startTime).TotalMilliseconds}\r\n".Dump();
}
else
{
    $"NOT FOUND: processing time: {(DateTime.Now - startTime).TotalMilliseconds}\r\n".Dump();
}

"From DB:".Dump();
startTime = DateTime.Now;
if (CommonPasswords.Where(c => System.Data.Linq.SqlClient.SqlMethods.Like(c.Word, password)).Count() > 0)
{
    $"FOUND: processing time: {(DateTime.Now - startTime).TotalMilliseconds}\r\n".Dump();
}
else
{
    $"NOT FOUND: processing time: {(DateTime.Now - startTime).TotalMilliseconds}\r\n".Dump();
}

"From DB:".Dump();
startTime = DateTime.Now;
if (CommonPasswords.Where(c => c.Word.ToLower() == password).Take(1).Any())
{
    $"FOUND: processing time: {(DateTime.Now - startTime).TotalMilliseconds}\r\n".Dump();
}
else
{
    $"NOT FOUND: processing time: {(DateTime.Now - startTime).TotalMilliseconds}\r\n".Dump();
}

Here is the translated SQL:

-- Region Parameters
DECLARE @p0 NVarChar(1000) = 'qwertyuiop123'
-- EndRegion
SELECT 
    (CASE 
        WHEN EXISTS(
            SELECT NULL AS [EMPTY]
            FROM [Security].[CommonPasswords] AS [t0]
            WHERE [t0].[Word] LIKE @p0
            ) THEN 1
        ELSE 0
     END) AS [value]
GO

-- Region Parameters
DECLARE @p0 NVarChar(1000) = 'qwertyuiop123'
-- EndRegion
SELECT COUNT(*) AS [value]
FROM [Security].[CommonPasswords] AS [t0]
WHERE [t0].[Word] LIKE @p0
GO

-- Region Parameters
DECLARE @p0 NVarChar(1000) = 'qwertyuiop123'
-- EndRegion
SELECT 
    (CASE 
        WHEN EXISTS(
            SELECT NULL AS [EMPTY]
            FROM (
                SELECT TOP (1) NULL AS [EMPTY]
                FROM [Security].[CommonPasswords] AS [t0]
                WHERE LOWER([t0].[Word]) = @p0
                ) AS [t1]
            ) THEN 1
        ELSE 0
     END) AS [value]

You can see that ANY wraps the query up in another layer of code to do a CASE Where Exists Then 1 where as Count() just adds in a Count command. Problem with both of these is you can't do a Top(1) but I can't see a better way using Top(1)

Results:

From DB: FOUND: processing time: 13.3962

From DB: FOUND: processing time: 12.0933

From DB: FOUND: processing time: 787.8801

Again:

From DB: FOUND: processing time: 13.3878

From DB: FOUND: processing time: 12.6881

From DB: FOUND: processing time: 780.2686

Again:

From DB: FOUND: processing time: 24.7081

From DB: FOUND: processing time: 23.6654

From DB: FOUND: processing time: 699.622

Without Index:

From DB: FOUND: processing time: 2395.1988

From DB: FOUND: processing time: 390.6334

From DB: FOUND: processing time: 664.8581

Now some of you may be thinking it's only a millisecond or two. However the varience was much greater before I put an index on it; by a few seconds.

The last calculation is there as I started with the notion that ToLower() would be faster than LIKE, and I was right, until I tried count and put an Index on it. I guess the Lower() makes the index irrelavent.