Timeout in IO operation

Question

I was using a query which pulled back a large amount of populated navigation properties. Essentially it looked like this:

using( var context = new MyApplicationContext() )
{
    DbSet<BaseTable> dbSet = context.Set<BaseTable>();
    IQueryable<BaseTable> query = dbSet;

    query = query.Include(entity => entity.T.C);
    query = query.Include(entity => entity.TC.Select(tc => tc.T.M));
    query = query.Include(entity => entity.TC);
    query = query.Include(entity => entity.W.FW.F.S);
    query = query.Include(entity => entity.W.FW.P);
    query = query.Include(entity => entity.W.PL.P);
    query = query.Include(entity => entity.W.PL);
    query = query.Include(entity => entity.W.E);
    query = query.Include(entity => entity.E);

    query = query.Where( set of conditions );

    List<BaseTable> Loaded = query.ToList();
}

However, running this code produced an error

Timeout in IO operation
[TimeoutException: Timeout in IO operation] MySql.Data.MySqlClient.TimedStream.StopTimer() +168
MySql.Data.MySqlClient.TimedStream.Read(Byte[] buffer, Int32 offset, Int32 count) +148
System.IO.BufferedStream.Read(Byte[] array, Int32 offset, Int32 count) +262
MySql.Data.MySqlClient.MySqlStream.ReadFully(Stream stream, Byte[] buffer, Int32 offset, Int32 count) +86
MySql.Data.MySqlClient.MySqlStream.LoadPacket() +110
MySql.Data.MySqlClient.MySqlStream.ReadPacket() +59
MySql.Data.MySqlClient.NativeDriver.GetResult(Int32& affectedRow, Int64& insertedId) +100
MySql.Data.MySqlClient.Driver.GetResult(Int32 statementId, Int32& affectedRows, Int64& insertedId) +54
MySql.Data.MySqlClient.Driver.NextResult(Int32 statementId, Boolean force) +145
MySql.Data.MySqlClient.MySqlDataReader.NextResult() +524
MySql.Data.MySqlClient.MySqlCommand.ExecuteReader(CommandBehavior behavior) +1939

How can I allow this query to have enough time to load?

Solution 1

What you need to do is increase the command timeout property.

In entity framework, the context used inherits from DbContext. This is an IObjectContextAdapter but does not contain easy access to the ObjectContext methods. As such, in order to access the command timeout property, you will need to go through the Database property of the DbContext.

using( var context = new MyApplicationContext() )
{
    DbSet<BaseTable> dbSet = context.Set<BaseTable>();
    IQueryable<BaseTable> query = dbSet;

   //set an increased command timeout by accessing Database property
   //on the context
   context.Database.CommandTimeout = 300;//in seconds (5 minutes)

    query = query.Include(entity => entity.T.C);
    query = query.Include(entity => entity.TC.Select(tc => tc.T.M));
    query = query.Include(entity => entity.TC);
    query = query.Include(entity => entity.W.FW.F.S);
    query = query.Include(entity => entity.W.FW.P);
    query = query.Include(entity => entity.W.PL.P);
    query = query.Include(entity => entity.W.PL);
    query = query.Include(entity => entity.W.E);
    query = query.Include(entity => entity.E);

    query = query.Where( set of conditions );

    List<BaseTable> Loaded = query.ToList();
}

Now your query can have the time it needs in order to pull a larger amount of data if necessary.

Solution 2

use this code in your Connection String in web.config

"default command timeout=0"

 "server=localhost;Database=Database;uid=;pwd=;Allow User Variables=True;Convert Zero Datetime=True;default command timeout=0"

Comments

[chriszumberge]

do you know if it's sufficient to specify the Default Command Timeout in the connection string? Or must it also be specified as you showed above?

[Travis J]

@chriszumberge - This is just to show a variable setting in code. You can also set it in the connection string, but the idea was that you probably don't always want this long timeout to be set.