Travis J Travis J - 6 months ago 39
SQL Question

Timeout in IO operation

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?

Answer

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.