Does running a stored procedure via Entity Framework with Async ignore the context.timeout

Question

I try looking for tutorial on running a stored procedure async via EF, but didn't find good one.

I have the following function that call stored procedure async

1. Is this code really async?
2. My function is failing because of timeout, why does EF care about timeout when function is async?
3. Can I tell EF to ignore timeout when using async functions?

This is my code:

public async Task GenerateQueueAsync()    
{
    await Task.Run(() => ((MyEntities)_context).GenerateQueue());
}

Answer 1

1. No it's not.

2. It is the way EF is designed! One reason could be to prevent endless deadlocks. But as answered in number 3 you can modify the timeout.

3. You can set the context CommandTimeout to 0 in Entity Framework to tell it to wait indefinitely. In order to set this value in different EF versions:

Entity Framework Core 2.0: The IDesignTimeDbContextFactory is introduced:

public class SampleContextFactory : IDesignTimeDbContextFactory<SampleContext>
{
    public SampleContext CreateDbContext(string[] args)
    {
        var optionsBuilder = new DbContextOptionsBuilder<SampleContext>();
        optionsBuilder.UseSqlServer(
            @"Server=.\;Database=db;Trusted_Connection=True;", 
            opts => opts.CommandTimeout(0)
            );

        return new SampleContext(optionsBuilder.Options);
    }
}

And you must ensure that your context has a constructor that takes a DbContextOptions object as a parameter:

public SampleContext(DbContextOptions options) : base(options) { }

Entity Framework Core 1.0:

context.Database.SetCommandTimeout(0);

Entity Framework 6:

context.Database.CommandTimeout = 0;

Note The existing capability offered by EF to modify the query execution timeout, does not mean that it should be done in all cases. The query execution timeout should be set as high as the successful execution of most queries could be guaranteed and also as low as the queries won't execute and waste or even in some cases lock the resources endlessly.

There are several things that you can do before increasing the timeout such as:

Query optimization:

• Avoiding loops either in TSQL or EF by using joins.

• Proper Bulk Operation implementation using proper methods in TSQL like SQL bulk-insert or efficient EF libraries such as EF bulk-insert

• Being aware of Lazy, Eager and Explicit Loading in EF and using them properly

Hosting machine software and hardware enhancement

Modifying your database tables to have proper indexing

In the end according to this answer using async/await with EF is a very bad idea for performance optimization.