LINQ and .COUNT timing out

Question

I have a general question, and curiosity about LINQ and timeouts.

I have a live application running and I am receiving timeouts on the following code.

The following code, which is normal, and I don't see anything wrong:

private static tblUser GetUserLinq(string email, string password) { DataContext db = new DataContext();

        var tblUsers = from user in db.tblUsers
                           where user.EmailAddress == email
                           && user.Password == password
                           select user;

        if (tblUsers.Count() == 0)
            return null;

        return tblUsers.First();
    }

But getting the following timeouts, quite a few, on:

        if (tblUsers.Count() == 0)

Here is the Exception

Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding. - System.Data.SqlClient.SqlException: Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding. at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection) at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection) at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj) at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj) at System.Data.SqlClient.SqlDataReader.ConsumeMetaData() at System.Data.SqlClient.SqlDataReader.get_MetaData() at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString) at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async) at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result) at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method) at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior, String method) at System.Data.SqlClient.SqlCommand.ExecuteDbDataReader(CommandBehavior behavior) at System.Data.Common.DbCommand.ExecuteReader() at System.Data.Linq.SqlClient.SqlProvider.Execute(Expression query, QueryInfo queryInfo, IObjectReaderFactory factory, Object[] parentArgs, Object[] userArgs, ICompiledSubQuery[] subQueries, Object lastResult) at System.Data.Linq.SqlClient.SqlProvider.ExecuteAll(Expression query, QueryInfo[] queryInfos, IObjectReaderFactory factory, Object[] userArguments, ICompiledSubQuery[] subQueries) at System.Data.Linq.SqlClient.SqlProvider.System.Data.Linq.Provider.IProvider.Execute(Expression query) at System.Data.Linq.DataQuery1.System.Linq.IQueryProvider.Execute[S](Expression expression) at System.Linq.Queryable.Count[TSource](IQueryable1 source) at Actions.GetUserLinq(String email, String password) in C:\Actions.cs:line 104 at Login(String email, String password) in C:\Actions.cs:line 33

Some of my thoughts on why it is timing out:

The count needs to count the number of records in that table, and that is not what we are after If we are trying to find if a record exists then should we be using the following code:

        var tblUsers = (from user in db.tblUsers
                           where user.EmailAddress == email
                           && user.Password == password
                           select user).FirstOrDefault(u => u.UserId <0) ;

Otherwise I am a little confused to why LINQ is timing out on a Count

One other thing I have noticed is that if you run the following SQL directly on the machine through SQL Enterprise Manager, it also times out, which suggests that it is perhaps a locking issue, however the only application calling this table is LINQ

select count(userid) from tbluser

Comments most welcome

Answer 1

the fact that it times out on running the query locally would suggest that you need some indexes on your table. If you run the query in the management studio with the "Exection Plan" enabled then you should be able to see what indexes need to be created.

The case may be that as the query is timing out then you won't get an execution plan, so you will have to use the "Estimate Execution Plan" function. (The icons for these look like little blue and green boxes linked together)

Answer 2

You should check your user table's indexes. It might be that a username/password index helps.

Answer 3

If the query is timing out even with SQL Enterprise Manager, that should be the first thing you address.

Take LINQ out of the picture entirely - make sure nothing else is accessing the database, and see if that query is still timing out. Run the query profiler on it.

Certainly if you just want to get the first user, you should rewrite your method as:

private static tblUser GetUserLinq(string email, string password) 
{ 
    DataContext db = new DataContext();

    var tblUsers = from user in db.tblUsers
                       where user.EmailAddress == email
                       && user.Password == password
                       select user;

    return tblUsers.FirstOrDefault();
}

(No need for a "u => u.UserId < 0" in there.)

FirstOrDefault will already return null if there are no results, that should be fine.

It sounds like you've got bigger problems with your database though. Are EmailAddress and Password both indexed appropriately?

Answer 4

As an aside, try to stay away from this sort of pattern:

  if (tblUsers.Count() == 0)
            return null;

There is a fantastic extension method called Any() which will generate much better SQL for you:

if ( !tblUsers.Any())
    return null;