linq timeout at ToList(), unable to use AsQueryable because of using DbContext

Question

I have been getting sporadic errors of linq time-out on the invocation of ToList() on a loosely-typed LINQ query. I want to optimize it by returning IQueryable from the method and enumerate it later. However, as a team policy, we are using the using statement with DbContext; therefore, once I exit the using clause, I cannot enumerate the IQueryable in the calling method.

My question is: is it advisable to shun the using statement with DbContext and just let EF handle the connection closing and garbage collection?
If yes, where is it best to initialize the context? When and where to dispose it of?

If not, how else to else to fix the timeout error on ToList()?

Thanks a lot.

Error I get:

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. 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() 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(CommandBehavior behavior)
at System.Data.Entity.Infrastructure.Interception.DbCommandDispatcher.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, Action`3 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)

Code:

using (MyEntities context = new MyEntities())
{
myQuery =       from tableOne in context.TableOne
join tableTwo in context.TableTwo
on tableOne.My_OID equals tableTwo.My_OID
join tableThree in context.TableThree
on tableTwo.Location_OID equals     tableThree.Location_OID
join tableFour in context.TableFour
on tableTwo.JobPosting_OID equals tableFour.JobPosting_OID
join tableFive in context.TableFive
on tableFour.Client_OID equals tableFive.Client_OID
where 
tableOne.Editable_f == true && tableOne.Active_f == true
&& tableTwo.Active_f == true
&& tableFive.Active_f == true
&& tableThree.Active_f == true
&& tableFour.Active_f == true
&& tableFive.Employee_OID == givenUserID
&& tableThree.Employee_OID == givenUserID
&& tableFive.SchemaName.Equals("MySchema")
&& tableThree.SchemaName.Equals("MySchema")
orderby tableOne.MyName ascending
select new MyDTO
{
PropOne = tableOne.My_OID,
PropTwo = tableOne.MyName,
PropThree = tableOne.Create_By
};

return myQuery.ToList<MyDTO>();

Finally, can I optimize the query itself in some way? I am only returning values from TableOne, so can I get rid of the joins with other table and use Any<> or some LINQ equivalent of the SQL EXISTS?

Any help is greatly appreciated. Thanks a lot!

Executing the query later won't make any difference. It only postpones the moment when you get the timeout. For the optimization, we need to see the classes and their navigation properties. You shouldn't have to join, normally. — Gert Arnold

Bartek Falkowski Bartek Falkowski · Accepted Answer · 2016-07-26T15:18:32

http://blog.jongallant.com/2012/10/do-i-have-to-call-dispose-on-dbcontext.html

This is the nice article about disposing EF dbcontext. In short, EF manages the connection for you and you don't have to do it manually. However, I would recommend to optimize query before you'll try to iterate through IQueryable after closing DbContext. Use Visual Studio Intellitrace or other profiler to capture Sql Server statements and see what is wrong with your query. There are also some optimization tricks in Entity Framework, e.g. eager loading - Include() method or AsNoTracking() method.

linq timeout at ToList(), unable to use AsQueryable because of using DbContext

1 Answers