Entity Framework Long Timeout on Queries from Azure PostgresSQL DB

Question

I am having an issue with excessivly long timeouts with calls to an Azure PostgresSQL server. On rare occasions, the call to the database will take approximately 15 minutes before timing out. From what we can tell looking at the Azure Portal, the query is not actually even starting. Here is an example of one of the queries:

System system = await (from r in _posSystemManagerContext.System
                       where r.StoreNumber == storeNumber
                          && r.MacAddress.ToLower() == macAddress.ToLower()
                       select r).SingleOrDefaultAsync();

This query happens all the time, and typically completes in less than 100ms. And other queries from other services are all happening at this time too with no issues. But this one (and a few others) will randomly get an error like this:

Failed executing DbCommand (944,756ms) [Parameters=[@__storeNumber_0='?' (DbType = Int32), @__ToLower_1='?'], CommandType='Text', CommandTimeout='30'] SELECT r."StoreNumber", r."MacAddress", r."AddedBy", r."DateAdded", r."DateLastModified", r."LastAccess", r."LastModifiedBy", r."SystemNumber", r."State" FROM "System" AS r WHERE (r."StoreNumber" = @__storeNumber_0) AND (LOWER(r."MacAddress") = @__ToLower_1) LIMIT 2

An exception occurred while iterating over the results of a query for context type 'PosSystemManager.Data.Contexts.PosSystemManagerContext'.
System.InvalidOperationException: An exception has been raised that is likely due to a transient failure. --->
Npgsql.NpgsqlException (0x80004005): Exception while reading from stream --->
System.IO.IOException: Unable to read data from the transport connection: Connection timed out. --->
System.Net.Sockets.SocketException (110): Connection timed out
--- End of inner exception stack trace ---
at System.Net.Sockets.Socket.AwaitableSocketAsyncEventArgs.ThrowException(SocketError error, CancellationToken cancellationToken)
at System.Net.Sockets.Socket.AwaitableSocketAsyncEventArgs.GetResult(Int16 token)
at System.Net.Security.SslStream.<FillBufferAsync>g__InternalFillBufferAsync|215_0[TReadAdapter](TReadAdapter adap, ValueTask`1 task, Int32 min, Int32 initial)
at System.Net.Security.SslStream.ReadAsyncInternal[TReadAdapter](TReadAdapter adapter, Memory`1 buffer)
at Npgsql.NpgsqlReadBuffer.<>c__DisplayClass34_0.<<Ensure>g__EnsureLong|0>d.MoveNext()
at Npgsql.NpgsqlReadBuffer.<>c__DisplayClass34_0.<<Ensure>g__EnsureLong|0>d.MoveNext()
--- End of stack trace from previous location where exception was thrown ---
at Npgsql.NpgsqlConnector.<>c__DisplayClass160_0.<<DoReadMessage>g__ReadMessageLong|0>d.MoveNext()
--- End of stack trace from previous location where exception was thrown ---
at Npgsql.NpgsqlConnector.<>c__DisplayClass160_0.<<DoReadMessage>g__ReadMessageLong|0>d.MoveNext()
--- End of stack trace from previous location where exception was thrown ---
at Npgsql.NpgsqlDataReader.NextResult(Boolean async, Boolean isConsuming)
at Npgsql.NpgsqlCommand.ExecuteReaderAsync(CommandBehavior behavior, Boolean async, CancellationToken cancellationToken)
at Npgsql.NpgsqlCommand.ExecuteDbDataReaderAsync(CommandBehavior behavior, CancellationToken cancellationToken)
at Microsoft.EntityFrameworkCore.Storage.RelationalCommand.ExecuteReaderAsync(RelationalCommandParameterObject parameterObject, CancellationToken cancellationToken)
at Microsoft.EntityFrameworkCore.Storage.RelationalCommand.ExecuteReaderAsync(RelationalCommandParameterObject parameterObject, CancellationToken cancellationToken)
at Microsoft.EntityFrameworkCore.Storage.RelationalCommand.ExecuteReaderAsync(RelationalCommandParameterObject parameterObject, CancellationToken cancellationToken)
at Microsoft.EntityFrameworkCore.Query.Internal.QueryingEnumerable`1.AsyncEnumerator.InitializeReaderAsync(DbContext _, Boolean result, CancellationToken cancellationToken)
at Npgsql.EntityFrameworkCore.PostgreSQL.Storage.Internal.NpgsqlExecutionStrategy.ExecuteAsync[TState,TResult](TState state, Func`4 operation, Func`4 verifySucceeded, CancellationToken cancellationToken)
--- End of inner exception stack trace ---
at Npgsql.EntityFrameworkCore.PostgreSQL.Storage.Internal.NpgsqlExecutionStrategy.ExecuteAsync[TState,TResult](TState state, Func`4 operation, Func`4 verifySucceeded, CancellationToken cancellationToken)
at Microsoft.EntityFrameworkCore.Query.Internal.QueryingEnumerable`1.AsyncEnumerator.MoveNextAsync()

Our connection string simply has the host, port, user, password, Ssl Mode (Require) and Maximum Pool Size (10).

The application is .NET Core 3.1, with Npgsql.EntityFrameworkCore.PostgreSQL version 3.1.4. The database is an 8 vCore General Purpose Azure Database for PostgresSQL running version 11.12.

Any idea why this would hang for 15 minutes when the default connection timeout is 15 seconds and the default command timeout is 30 seconds? Any help would be appreciated. Thank you.

Answer 1

After upgrading the applications to .NET 6 and Npgsql 6.0.4, all timeouts (and the reason for the timeouts is a whole different problem) are now only taking 30 seconds as expected.