0

I'm using C# Web API on .Net6 (not core) to access a SQL Server database on Azure. Sometimes the database connection gets a timeout in 30 seconds.

In the Main I do this...

var builder = WebApplication.CreateBuilder(args);
builder.Services.AddDbContextPool<MyContext>(options => options.UseSqlServer(ConnStr));
var app = builder.Build();

I tried to change the timeout in MyContext constructor...

public MyContext(DbContextOptions<MyContext> options) : base(options)
{
    Database.SetCommandTimeout(230);    // 230 is the maximum allowed by azure
}

Sometimes this helps, but sometimes I still see timeout in 30 sec. Perhaps it is because the base(options) is taking too long.

My workaround right now is to create a "ping" API that returns hello world, and then the client must call "ping" to "wakeup" the server before attempting anything else. Obviously not an ideal solution.

Any advice how to fix better? Is it possible to set the timeout to 230 earlier, i.e. before calling the base(options) ?

John Henckel
  • 10,274
  • 3
  • 79
  • 79
  • If you are using LocalDb then it automatically shuts down after 10 minutes. See https://stackoverflow.com/a/76374441/14868997 – Charlieface Jun 05 '23 at 15:30
  • You could try overriding `OnConfiguring` in your `DbContext`. It'll be called inside `base(options)`. It seems like the only solution, because *doing something before calling the constructor initializer is not possible*. – SNBS Jun 05 '23 at 15:42
  • 1
    There is a connection complete timeout and a command timeout. The 30 second timeout may be the connection never completed. Check the log files in server by using SQL Serve Management Studio and looking in the explorer under Management. – jdweng Jun 05 '23 at 16:21
  • Azure also has an auto pause feature, which you can extend – Charlieface Jun 05 '23 at 19:53

1 Answers1

0

There is a CommandTimeout and a ConnectionTimeout, and they are two distinct things. The code in the question is setting the CommandTimeout, but it's likely the ConnectionTimeout is what's actually trippig you up.

The ConnectionTimeout can be set as part of the connection string, or by accessing the DBContext's underlying ADO.Net DbConnection (DbContext.ObjectContext.Connection)

Joel Coehoorn
  • 399,467
  • 113
  • 570
  • 794