Failed executing DbCommand because timeout expired .net core

Question

My goal is to provide simple API to retrieve data from Payments (~400 rows) table which consists 5 columns.

Payment: Id (int),
PaymentsNumber (tinyint),
Rate (decimal(18,2)),
ProductType (tinyint),
ClientClubType (tinyint).

Users can make posts request with with the request params of (should return ~12 rows):

PaymentsRequest 
{
    public int? PaymentsNumber { get; set; }
    public byte? ProductType { get; set; }
    public byte? ClientClubType { get; set; }
}

Using EF-Core:

services.AddDbContext<MyContext>(cfg => cfg.UseSqlServer(Configuration.GetConnectionString(...),optionsBuilder => optionsBuilder.CommandTimeout(60)));

public async Task<IEnumerable<Payments>> GetPaymentsAsync(PaymentsRequest request)
{
    IQueryable<Payments> query = this._context.Set<Payments>();
    query = query.Where(filter => 
                        (request.ClientClubType == null || filter.ClientClubType == request.ClientClubType) &&
                        (request.ProductType == null || filter.ProductType == request.ProductType) &&
                        (request.PaymentsNumber == null || filter.PaymentsNumber == request.PaymentsNumber));

    return await query.ToListAsync();
}

On azure application insights I can see 2 consecutive logs, caused by the same exception:

1. Log1: Failed executing DbCommand.
2. Log2: Execution Timeout Expired. The timeout period elapsed prior to completion of the operation or the server is not responding.

The Log1 is (while there is no need to write here the log2):

Failed executing DbCommand (65,238ms) [Parameters=[@__request_ClientClubType_0='?' (Size = 1) (DbType = Byte), @__request_ProductType_1='?' (Size = 1) (DbType = Byte)], CommandType='Text', CommandTimeout='60']

SELECT [p].[Id], [p].[ClientClubType], [p].[PaymentsNumber], [p].[ProductType], [p].[Rate] FROM [Payments] AS [p] WHERE (([p].[ClientClubType] = @__request_ClientClubType_0) AND @__request_ClientClubType_0 IS NOT NULL) AND (([p].[ProductType] = @__request_ProductType_1) AND @__request_ProductType_1 IS NOT NULL)

My application is a .net core 3.0 application deployed on azure linux webapp.

The issue only occurs from production not every time and I can not reconstruct the issue from MSSMS. Any idea?

UPDATE:

After @panagiotis-kanavos commented, I've updated my code to:

services.AddDbContextPool<MyContext>(cfg => cfg.UseSqlServer(Configuration.GetConnectionString(...),optionsBuilder => optionsBuilder.CommandTimeout(60)));

public async Task<IEnumerable<Payments>> GetPaymentsAsync(PaymentsRequest request)
{
    IQueryable<Payments> query = this._context.Payments;
    query = query.Where(filter => 
                        (filter.ClientClubType == request.ClientClubType) &&
                        (filter.ProductType == request.ProductType) &&
                        (filter.PaymentsNumber == request.PaymentsNumber));

    return await query.ToListAsync();
}

Answer 1

• Your timeout is 60 seconds this could be increased. It's usually not a good idea to do this as it will hide other issues.
• If the table has lots of writes, or something has a long running transaction, it can block/contend with your query.
• If you query is part of a larger begin transaction - end transaction sequence of SQL operations, it may be blocked by other open transactions
• Same as last one - If multiple calls are made at the same time/nearly the same time to this query it could slow down processing of each query. I've seen this where a web front end populates a screen of 20 lines of data where each line is an different call to the same web-api endpoint for the same type of data. For example, getting the monthly transaction dollar total for each of the last 12 months.