Entity Framework ExecuteStoreCommands Exception during FirstOrDefault method

Question

Occasionally, while debugging some older application code, we receive an exception.

This is the offending line of code:

XMLReferenceSequence lastXml = (from xml in _entities.XMLReferenceSequences
                                        where xml.ServiceId == aService.Id
                                        where xml.SourceTypeId == ETrainsSourceType.SOURCE_RAIL_XML
                                        orderby xml.ReferenceDateTime descending
                                        select xml).FirstOrDefault();

It should be noted that the code does work most of the time and this does happen rarely.

Any thoughts on how to solve this issue would be appreciated. Was considering reformatting the code to perhaps do a ToList() first before FirstOrDefault()?

Stack trace of the exception:

Error processing Rail record. : 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.ObjectQuery1.<>c__DisplayClass7.<GetResults>b__6() at System.Data.Entity.Core.Objects.ObjectContext.ExecuteInTransaction[T](Func1 func, IDbExecutionStrategy executionStrategy, Boolean startLocalTransaction, Boolean releaseConnectionOnSuccess) at System.Data.Entity.Core.Objects.ObjectQuery1.<>c__DisplayClass7.<GetResults>b__5() at System.Data.Entity.Infrastructure.DbExecutionStrategy.Execute[TResult](Func1 operation) at System.Data.Entity.Core.Objects.ObjectQuery1.GetResults(Nullable1 forMergeOption) at System.Data.Entity.Core.Objects.ObjectQuery1.<System.Collections.Generic.IEnumerable<T>.GetEnumerator>b__0() at System.Data.Entity.Internal.LazyEnumerator1.MoveNext() at System.Linq.Enumerable.FirstOrDefault[TSource](IEnumerable1 source) at System.Data.Entity.Core.Objects.ELinq.ObjectQueryProvider.<GetElementFunction>b__1[TResult](IEnumerable1 sequence) at System.Data.Entity.Core.Objects.ELinq.ObjectQueryProvider.ExecuteSingle[TResult](IEnumerable1 query, Expression queryRoot) at System.Data.Entity.Core.Objects.ELinq.ObjectQueryProvider.System.Linq.IQueryProvider.Execute[TResult](Expression expression) at System.Data.Entity.Internal.Linq.DbQueryProvider.Execute[TResult](Expression expression) at System.Linq.Queryable.FirstOrDefault[TSource](IQueryable1 source) at CHO.ETrains.Business.ETrainsManager.MovementProcessingFromRailList(ETrainFeedDto aFeed) in F:\agent_work\4\s\CHO.ALL\Business\CHO.ETrains.Business\ETrainsManager.cs:line 1961 at CHO.ETrains.Business.ETrainsManager.ProcessMessageOffServiceBus(ETrainFeedDto eTrainFeed, RequestDto req, HashSet`1& changesDetected, String schemaDir) in F:\agent_work\4\s\CHO.ALL\Business\CHO.ETrains.Business\ETrainsManager.cs:line 379

Received Xml message which could not be processed:System.Data.Entity.Core.EntityCommandExecutionException: An error occurred while executing the command definition. See the inner exception for details. ---> System.Data.SqlClient.SqlException: Execution 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, Action1 wrapCloseInAction) at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection, Action1 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, Boolean isInternal, Boolean forDescribeParameterEncryption) at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async, Int32 timeout, Task& task, Boolean asyncWrite, Boolean inRetry, SqlDataReader ds, Boolean describeParameterEncryptionRequest) at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, TaskCompletionSource1 completion, Int32 timeout, Task& task, Boolean& usedCache, Boolean asyncWrite, Boolean inRetry) 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(CommandBehavior behavior) at System.Data.Entity.Infrastructure.Interception.DbCommandDispatcher.<Reader>b__c(DbCommand t, DbCommandInterceptionContext1 c) at System.Data.Entity.Infrastructure.Interception.InternalDispatcher1.Dispatch[TTarget,TInterceptionContext,TResult](TTarget target, Func3 operation, TInterceptionContext interceptionContext, Action3 executing, Action3 executed) at System.Data.Entity.Infrastructure.Interception.DbCommandDispatcher.Reader(DbCommand command, DbCommandInterceptionContext interceptionContext) at System.Data.Entity.Internal.InterceptableDbCommand.ExecuteDbDataReader(CommandBehavior behavior) at System.Data.Common.DbCommand.ExecuteReader(CommandBehavior behavior) 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.ObjectQuery1.<>c__DisplayClass7.<GetResults>b__6() at System.Data.Entity.Core.Objects.ObjectContext.ExecuteInTransaction[T](Func1 func, IDbExecutionStrategy executionStrategy, Boolean startLocalTransaction, Boolean releaseConnectionOnSuccess) at System.Data.Entity.Core.Objects.ObjectQuery1.<>c__DisplayClass7.<GetResults>b__5() at System.Data.Entity.Infrastructure.DbExecutionStrategy.Execute[TResult](Func1 operation) at System.Data.Entity.Core.Objects.ObjectQuery1.GetResults(Nullable1 forMergeOption) at System.Data.Entity.Core.Objects.ObjectQuery1.<System.Collections.Generic.IEnumerable<T>.GetEnumerator>b__0() at System.Data.Entity.Internal.LazyEnumerator1.MoveNext() at System.Linq.Enumerable.FirstOrDefault[TSource](IEnumerable1 source) at System.Data.Entity.Core.Objects.ELinq.ObjectQueryProvider.<GetElementFunction>b__1[TResult](IEnumerable1 sequence) at System.Data.Entity.Core.Objects.ELinq.ObjectQueryProvider.ExecuteSingle[TResult](IEnumerable1 query, Expression queryRoot) at System.Data.Entity.Core.Objects.ELinq.ObjectQueryProvider.System.Linq.IQueryProvider.Execute[TResult](Expression expression) at System.Data.Entity.Internal.Linq.DbQueryProvider.Execute[TResult](Expression expression) at System.Linq.Queryable.FirstOrDefault[TSource](IQueryable1 source) at CHO.ETrains.Business.ETrainsManager.MovementProcessingFromRailList(ETrainFeedDto aFeed) in F:\agent_work\4\s\CHO.ALL\Business\CHO.ETrains.Business\ETrainsManager.cs:line 1961 at CHO.ETrains.Business.ETrainsManager.ProcessMessageOffServiceBus(ETrainFeedDto eTrainFeed, RequestDto req, HashSet`1& changesDetected, String schemaDir) in F:\agent_work\4\s\CHO.ALL\Business\CHO.ETrains.Business\ETrainsManager.cs:line 393 at CHO.WebJobs.xmlReceive.MessageProcessor.ProcessBrokeredMessage(BrokeredMessage msg) in F:\agent_work\4\s\CHO.WebJobs\CHO.WebJobs.xmlReceive\MessageProcessor.cs:line 102

seems like the second `where` should be an `&&`, but obviously that's not the problem. Does the `ToList()` call resolve the issue, or it still continues? — Rufus L, Jun 26 '17 at 23:23
Hi Rufus, I hadn't tried the seperate ToList. Was just curious to see if anyone had seen this before. I might change the where to be &&. Perhaps the multiple where statements is complicating the query also. I think I read somewhere EF may turn that into multilple subqueries. — Stefan Zvonar, Jun 26 '17 at 23:47
var lastXmlRecord = _entities.XMLReferenceSequences .Where(x => x.TrainServiceId == aService.Id) .Where(x => x.SourceTypeId == ETrainsSourceType.SOURCE_RAIL_XML) .OrderByDescending(x => x.ReferenceDateTime) .ToList() .FirstOrDefault(); — Stefan Zvonar, Jun 26 '17 at 23:48
Changing to the above code, changed to method syntax, and whacked in the ToList to be safe. Have added extra logging also around it. Will see what happens ... — Stefan Zvonar, Jun 26 '17 at 23:49

score 1 · Accepted Answer · answered Jun 27 '17 at 00:09

Your error message indicates your method failed to execute in time.

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

Sql Connections connection have a CommandTimeout property, which by default is 30 seconds. If your query doesn't complete within this time the exception above is thrown. You can increase this value in your connection string if you know that you have a long running query that might exceed this limit. See this: https://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlcommand.commandtimeout(v=vs.110).aspx

Ideally, you can fix this problem by to determining whether it is your query or another query that is causing the problem. If it is your query, optimizing the data with a maintenance plan and/or creating indexes can speed up the query.

Another potential hazard is a long running query that locks one of the tables you are pulling data from. If this is the case you can performing a query that require the data you are pulling doesn't have a lock on it. This post shows how to do this entity framework. Entity Framework with NOLOCK

thanks mate, for some reason my eyeballs didn't even see the timeout. I was buried deep into thinking it was more sinister than that. — Stefan Zvonar, Jun 27 '17 at 01:51

Entity Framework ExecuteStoreCommands Exception during FirstOrDefault method

1 Answers1