Scalar function in LinqToSql

Question

In ADO.Net/SQLClient I would often do something like this:

SELECT COUNT(*) FROM SomeTable WHERE SomeKey = 1234

...and fire it using executescalar to return the value of count - for a simple check if something exists.

How would I do the same using LinqToSql?

Answer 1

if (context.SomeTable.Any(row => row.SomeKey == 1234))
{
    DoStuff();
}

You could also use Count().

if (context.SomeTable.Count(row => row.SomeKey == 1234) > 0)
{
    DoStuff();
}

But this requires always to go through all rows while Any() can return after the first matching row - so Any() might have better performance.

Answer 2

Remember, Linq to Sql is deferred execution, which means, the query only actually executes when you access the collection. Therefore:

var q = (from p in db.SomeTable
        where p.SomeKey == 1234
        select p).Count();

Will turn into a SELECT Count() on the SQL side of things.

Answer 3

If you're looking to see if something exists you can use the any function:

if (context.SomeTable.Any(i => i.SomeKey == 1234))
{
    return true;
}

or if you actually want to know that count you can use the where function and the count function:

context.SomeTable.Where(i=> i.SomeKey == 1234).Count();