EF Core Command Timeout Not Taking Effect

Question

I am finding that neither the DatabaseFacade.SetCommandTimeout method nor the SqlServerDbContextOptionsBuilder.CommandTimeout method are having any effect on my code and instead a long running SQL command just continues indefinitely.

My context object looks something like this:

public class MyDataContext : DbContext
{
    public MyDataContext()
    {
        base.Database.SetCommandTimeout(60);
    }

    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder) 
    {
        ...
        optionsBuilder.UseSqlServer(connection, sqlServerOptions => sqlServerOptions.CommandTimeout(60));
    }
}

But then when I execute the code in my web API:

using var dbContext = new MyDataContext();
var timeout = dbContext.Database.GetCommandTimeout(); // this, as expected, returns "60"
var listOfEntities = dbContext.MyLargeSetOfEntities.ToList(); // this, however, just runs way longer than a minute (it is returning an immense amount of data to test the timeout)

Is there somewhere that could be overriding this value and making it indefinite?

Hardshell

Enable SQL trace in EF Core. It shows CommandTimeout for each executed SQL. – Dauntless

Answer 1

The value is not overridden, but you have wrong expectation of what it does.

it is returning an immense amount of data to test the timeout

Command timeout is just for the ExecuteXYZ part of the database command, which for queries is ExecuteReader, i.e. is limiting the time of executing the SQL query (imagine some super complex SQL command with many joins, cartesian products, group by, subqueries etc. which might take forever to run. Or some accessed table is locked by another long running transaction etc.).

Once the SQL command is executed, consuming the result (in this case reading the returned data) is not limited in any way - there is just no such setting and couldn't be.

If you want to limit the time of the whole operation, consider combining the command timeout setting with the async cancellation support, for instance inside async method

var listOfEntities = await dbContext.MyLargeSetOfEntities
    .ToListAsync(new CancellationTokenSource(TimeSpan.FromSeconds(60)).Token);

Divot