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

c# linq-to-sql lambda
Protagonist picture Protagonist · Mar 16, 2009 · Viewed 58.2k times · Source

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?

Answer

Marc Gravell picture Marc Gravell · Mar 16, 2009

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;

Related questions

How do you perform a left outer join using linq extension methods

Assuming I have a left outer join as such: from f in Foo join b in Bar on f.Foo_Id equals b.Foo_Id into g from result in g.DefaultIfEmpty() select new { Foo = f, Bar = result } How would …

Read More
LINQ Expression to return Property value?

I'm trying to create a generic function to help me select thousands of records using LINQ to SQL from a local list. SQL Server (2005 at least) limits queries to 2100 parameters and I'd like to select more records than that. Here …

Read More
C# How to convert an Expression<Func<SomeType>> to an Expression<Func<OtherType>>

I have used C# expressions before based on lamdas, but I have no experience composing them by hand. Given an Expression<Func<SomeType, bool>> originalPredicate, I want to create an Expression<Func<OtherType, bool&…

Read More