Execution of SetCommandTimeout not working with EF Core 5 for PostgreSQL

user_10701047 0
Viewed 556

I have a problem when I SetCommandTimeout is like the method is not working properly.

I Use PostgreSQL as database, and for the EntityFramework Core I'm using Npgsql.EntityFrameworkCore.PostgreSQL with Version 5.0.5.1

In the code I Set Timeout for 1s like this context.Database.SetCommandTimeout(1); and I set a Stopwatch to check how much time it takes, but the ElapsedMiliseconds always return around 15000ms to 16000ms. so the SetCommandTimeout(1) is clearly not working.

I also tried using context.Database.SetCommandTimeout(TimeSpan.FromSeconds(1)); is not working either.

One more thing, I only want to Set Timeout for specific Request. So the other Request will have the default timeout.

Here's my code:

[Route("api/[controller]")]
[ApiController]
public class TestController : Controller
{
    private readonly DatabaseContext context;

    public TestController(DatabaseContext context)
    {
        this.context = context;
    }

    [AllowAnonymous]
    [HttpGet]
    public async Task<IActionResult> Test()
    {
        var sw = Stopwatch.StartNew();
        try
        {
            context.Database.SetCommandTimeout(1);
            var test = await context.Test.FirstOrDefaultAsync();
            return Ok();
        }
        catch (Exception)
        {
            return BadRequest();
        }
        finally
        {
            sw.Stop();
            var elapsed = sw.ElapsedMilliseconds; // always return around 15000ms to 16000ms
        }
    }
}

here's how I register it in Startup.Cs

services.AddDbContext<DatabaseContext>(options =>
{
    options.UseNpgsql(ConnectionString);
});

did I missing something?

Thanks in advance

npgsql entity-framework-core c#
2 Answers

The command timeout is the time a query / command is allowed to execute on the database server. The timer starts when the database server receives the request. Your end-to-end round trip time may be higher than the command timeout due to network throughput or other constraints - including resource exhaustion on your web server.

Command Timeout: The time to wait (in seconds) while trying to execute a command before terminating the attempt and generating an error. Source

user_53698520

Googled a little and found issues related to this library.

Based on this forum post, it seems that the issue is coming from the source code and the settings is just being ignored.

Try to set-up command timeout inside the creating of DB context instead right before usage.

Another one is at the github repository.

Solution for this one is to upgrade to latest version of drivers.

Another from github.

The workaround for this is to modify connection string during creation of context:

if (!connectionString.Contains("CommandTimeout"))
{
    connectionString += $";CommandTimeout=120";
}

EDIT (after OP mentioned he wants to use it on single request): I would recommend by going the way with creating other database context and modifying its connection string (as above). That way you would be able to execute short/long running commands. However, this brings other issues related to the multiple DB-context and possible mismatch of data. Needs to be handled with caution.

user_71675720
Related