Linq IEnumerable Error which occurs spontaneously

Question

I have this problem where I perform a Linq query then convert it to a List object. Let me add that this error only occurs sometimes and it happens in production. When the IEnumerable object is converted to a List an error is thrown. I have a logger set up and this is the exception type, stack trace, and inner exception. Any thoughts on how i can refactor this snippet of code to make it more stable would be much appreciated. Here is the code which throws the error

var isMember = db.DN2_Member.Where(m =>
    m.Partner_Contract_Nbr.ToLower() == member.DentalMemberId.ToLower()
    && m.Mbr_First_Nm.ToLower() == member.DentalMemberFirstName.ToLower()
    //&& m.Mbr_Mid_Nm.ToLower().Trim() == memberMiddleName.ToLower()
    && m.Mbr_Last_Nm.ToLower() == member.DentalMemberLastName.ToLower()
    && m.DW_Rec_End_Dt == null 
    && m.Mbr_Suc_Title.ToLower() == membersuffix.ToLower().Replace(".", ""));

//Forces the query to evaluate to a definitive collection; necessary for querying it further
var memberList = isMember.ToList();

Line 88 is var memberList = isMember.ToList();

Here is the Exception Details.

Exception Type: An error occurred while executing the command definition. See the inner exception for details.

Stack Trace: at System.Data.Entity.Core.EntityClient.Internal.EntityCommandDefinition.ExecuteStoreCommands(EntityCommand entityCommand, CommandBehavior behavior) at System.Data.Entity.Core.Objects.Internal.ObjectQueryExecutionPlan.Execute[TResultType](ObjectContext context, ObjectParameterCollection parameterValues) at System.Data.Entity.Core.Objects.ObjectContext.ExecuteInTransaction[T](Func`1 func, IDbExecutionStrategy executionStrategy, Boolean startLocalTransaction, Boolean releaseConnectionOnSuccess) at System.Data.Entity.Core.Objects.ObjectQuery`1.<>c__DisplayClass3.b__1() at System.Data.Entity.SqlServer.DefaultSqlExecutionStrategy.Execute[TResult](Func`1 operation) at System.Data.Entity.Core.Objects.ObjectQuery`1.GetResults(Nullable`1 forMergeOption) at System.Data.Entity.Core.Objects.ObjectQuery`1.b__0() at System.Data.Entity.Internal.LazyEnumerator`1.MoveNext() at System.Collections.Generic.List`1..ctor(IEnumerable`1 collection) at System.Linq.Enumerable.ToList[TSource](IEnumerable`1 source) at DentalTHSEnrollmentService.WebHost.THSService.GetMember(DentalMember member) in e:\GoAgent\pipelines\DentalTHSEnrollmentService_Master\product\app\DentalTHSEnrollmentService.WebHost\THSService.svc.cs:line 88

Inner Exception: System.Data.Entity.Core.EntityCommandExecutionException: An error occurred while executing the command definition. See the inner exception for details. ---> System.Data.SqlClient.SqlException: Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding. ---> System.ComponentModel.Win32Exception: The wait operation timed out --- End of inner exception stack trace --- at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction) at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose) at System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady) at System.Data.SqlClient.SqlDataReader.TryConsumeMetaData() 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, Int32 timeout, Task& task, Boolean asyncWrite, SqlDataReader ds) at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, TaskCompletionSource`1 completion, Int32 timeout, Task& task, Boolean asyncWrite) 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.Entity.Infrastructure.Interception.InternalDispatcher`1.Dispatch[TTarget,TInterceptionContext,TResult](TTarget target, Func`3 operation, TInterceptionContext interceptionContext, Action`3 executing, Action`3 executed) at System.Data.Entity.Infrastructure.Interception.DbCommandDispatcher.Reader(DbCommand command, DbCommandInterceptionContext interceptionContext) at System.Data.Entity.Core.EntityClient.Internal.EntityCommandDefinition.ExecuteStoreCommands(EntityCommand entityCommand, CommandBehavior behavior) --- End of inner exception stack trace --- at System.Data.Entity.Core.EntityClient.Internal.EntityCommandDefinition.ExecuteStoreCommands(EntityCommand entityCommand, CommandBehavior behavior) at System.Data.Entity.Core.Objects.Internal.ObjectQueryExecutionPlan.Execute[TResultType](ObjectContext context, ObjectParameterCollection parameterValues) at System.Data.Entity.Core.Objects.ObjectContext.ExecuteInTransaction[T](Func`1 func, IDbExecutionStrategy executionStrategy, Boolean startLocalTransaction, Boolean releaseConnectionOnSuccess) at System.Data.Entity.Core.Objects.ObjectQuery`1.<>c__DisplayClass3.b__1() at System.Data.Entity.SqlServer.DefaultSqlExecutionStrategy.Execute[TResult](Func`1 operation) at System.Data.Entity.Core.Objects.ObjectQuery`1.GetResults(Nullable`1 forMergeOption) at System.Data.Entity.Core.Objects.ObjectQuery`1.b__0() at System.Data.Entity.Internal.LazyEnumerator`1.MoveNext() at System.Collections.Generic.List`1..ctor(IEnumerable`1 collection) at System.Linq.Enumerable.ToList[TSource](IEnumerable`1 source) at DentalTHSEnrollmentService.WebHost.THSService.GetMember(DentalMember member) in e:\GoAgent\pipelines\DentalTHSEnrollmentService_Master\product\app\DentalTHSEnrollmentService.WebHost\THSService.svc.cs:line 88

_"Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding."_ - try searching. You need to analyze your queries and optimize them. — CodeCaster, Nov 17 '15 at 14:50
Also you might want to try a SQL Profiler trace, examine the execution plans of the generated query. — Mike G, Nov 17 '15 at 14:51
Seems there is a deadlock somewhere and the query is taking to long. — devzim, Nov 17 '15 at 14:51
Is there a significant amount of difference in the data between prod/test environments? — Mike G, Nov 17 '15 at 14:52
Query is run on same database as production no matter what environment. — devzim, Nov 17 '15 at 15:00
You could do all the `ToLower` and `Replace` on the `member` properties and `membersuffix` before running the query by assigning to local variables and using them in the query instead. — juharr, Nov 17 '15 at 15:00

score 1 · Accepted Answer · answered Nov 17 '15 at 14:51

1

The crux of your error message is:

System.Data.SqlClient.SqlException: Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding.

Essentially your sql query is taking longer to run than your command timeout. You should add indexing to your table / tables to help with the performance.

answered Nov 17 '15 at 14:51

Darren Gourley

1,798
11
11

The only thing strange about this error is I am unable to replicate it on my local machine and the search is done on the production database. The storing of the information is where the database differs between environments. Therefore, i should be able to replicate the query timeout because the query is run on the same database no matter what environment – devzim Nov 17 '15 at 14:57
You mentioned you are logging these errors. Check the dates and times of the errors and see if you can find any sort of correlation. Perhaps this only occurs at lunch time, or at 8pm (as an example) when your website is busiest. Your SQL server may not be able to handle the load that it is put under. – Darren Gourley Nov 17 '15 at 15:00
I am going to talk to a DBA and see if they can run some tool to get some stats for me, and they can add indexing to my table. Thank you for the recommendation. – devzim Nov 17 '15 at 15:09

Linq IEnumerable Error which occurs spontaneously

1 Answers1