.net 实体框架核心：Azure容器应用程序的执行超时已过期

我们遇到了EF Core的超时异常.

我们有一些正在工作的查询和一些不起作用的查询.我们正在将Azure Container应用程序与内部部署的SQL Server结合使用.

代码:这是我们正在执行的查询，它是不起作用的查询之一:

IQueryable<Disbursement> response = _dataContext.Disbursements
    .Where(w => w.DisbursementDate <= disbursementDate && !w.DeletedFlag && !w.Paid && w.MethodId == methodId && w.AccountId == accountId)
    .OrderByDescending(o => o.CreatedOnDateUtc);

var result = response.Skip((page - 1) * pageSize)
    .Take(pageSize)
    .ToList();

以下是一个工作查询的示例

IQueryable<AccountBillingModel> response = _dataContext.Disbursements.Where(w => w.DisbursementDate <= disbursementDate && !w.DeletedFlag && !w.Paid && w.MethodId == methodId)
    .Select(s => s.AccountId)
    .Distinct()
    .Select(s => new AccountBillingModel
    {
        AccountId = s
    });

return await response.ToListAsync();

这是模型

public class Disbursement
{
    public Guid Id { get; set; }
    public Guid TransactionId { get; set; }
    public Guid AccountId { get; set; }
    public Guid MethodId { get; set; }
    public string ExternalItemId { get; set; } = string.Empty;
    public string Description { get; set; } = string.Empty;
    public int Quantity { get; set; }
    public double PriceExcluding { get; set; }
    public bool Paid { get; set; }
    public DateTime DisbursementDate { get; set; }
    public DateTime? DisbursementRunDate { get; set; }
    public DateTime? AmendedOnDateUtc { get; set; }
    public bool DeletedFlag { get; set; } = false;
    public DateTime CreatedOnDateUtc { get; set; } = DateTime.UtcNow;
}

DbContext:

public class DataContext : DbContext
{
    public DataContext(DbContextOptions options) : base(options) { }
    public DbSet<Disbursement> Disbursements { get; set; }
}

如果我们使用docker在本地机器上运行这些代码，它就可以工作.

我们已经试过了.

• 实体框架核心8

• 实体框架核心6

• .NET core 6

• .NET core 8

我们甚至try 将异步呼叫(如ToListAsync())更改为正常的ToList()，但没有成功

我们所看到的是，如果我们使模型更小，即删除查询工作的一些列，基本上就像总是工作的第二个查询只 Select 一列.

一旦我们达到大约10列，我们就开始经历超时.当我们将模型保留为原样时，我们看到的是相同的情况，但只指定了10列作为Select()列的一部分

表定义:

CREATE TABLE [dbo].[Disbursements] 
(
    [Id]                UNIQUEIDENTIFIER CONSTRAINT [DF_Disbursements_Id] DEFAULT (newid()) NOT NULL,
    [AccountId]         UNIQUEIDENTIFIER NOT NULL,
    [MethodId]          UNIQUEIDENTIFIER NOT NULL,
    [ExternalItemId]    NVARCHAR(50)     NOT NULL,
    [Description]       NVARCHAR(150)    NOT NULL,
    [Quantity]          INT              NOT NULL,
    [PriceExcluding]    FLOAT (53)       NOT NULL,
    [DisbursementDate]  DATETIME         NOT NULL,
    [Paid]              BIT              CONSTRAINT [DF_Disbursements_Paid] DEFAULT ((0)) NOT NULL,
    [AmendedOnDateUtc]  DATETIME         NULL,
    [CreatedOnDateUtc]  DATETIME         NOT NULL,
    [DeletedFlag]       BIT              CONSTRAINT [DF_Disbursements_DeletedFlag] DEFAULT ((0)) NOT NULL,
    [DisbursementRunDate] DATETIME NULL, 

    CONSTRAINT [PK_Disbursements] PRIMARY KEY CLUSTERED ([Id] ASC)
);

堆栈跟踪

An exception occurred while iterating over the results of a query for context type \u0027****\u0027.\nMicrosoft.Data.SqlClient.SqlException (0x80131904): Execution Timeout Expired.  The timeout period elapsed prior to completion of the operation or the server is not responding.\n ---\u003E System.ComponentModel.Win32Exception (258): Unknown error 258\n  
   at Microsoft.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection, Action\u00601 wrapCloseInAction)\n  
   at Microsoft.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose)\n  
   at Microsoft.Data.SqlClient.TdsParserStateObject.ThrowExceptionAndWarning(Boolean callerHasConnectionLock, Boolean asyncClose)\n  
   at Microsoft.Data.SqlClient.TdsParserStateObject.ReadSniError(TdsParserStateObject stateObj, UInt32 error)\n  
   at Microsoft.Data.SqlClient.TdsParserStateObject.ReadSniSyncOverAsync()\n  
   at Microsoft.Data.SqlClient.TdsParserStateObject.TryReadNetworkPacket()\n  
   at Microsoft.Data.SqlClient.TdsParserStateObject.TryPrepareBuffer()\n  
   at Microsoft.Data.SqlClient.TdsParserStateObject.TryReadByte(Byte\u0026 value)\n  
   at Microsoft.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean\u0026 dataReady)\n  
   at Microsoft.Data.SqlClient.SqlDataReader.TryConsumeMetaData()\n  
   at Microsoft.Data.SqlClient.SqlDataReader.get_MetaData()\n  
   at Microsoft.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString, Boolean isInternal, Boolean forDescribeParameterEncryption, Boolean shouldCacheForAlwaysEncrypted)\n  
   at Microsoft.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean isAsync, Int32 timeout, Task\u0026 task, Boolean asyncWrite, Boolean inRetry, SqlDataReader ds, Boolean describeParameterEncryptionRequest)\n  
   at Microsoft.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, TaskCompletionSource\u00601 completion, Int32 timeout, Task\u0026 task, Boolean\u0026 usedCache, Boolean asyncWrite, Boolean inRetry, String method)\n  
   at Microsoft.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method)\n  
   at Microsoft.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior)\n  
   at Microsoft.Data.SqlClient.SqlCommand.ExecuteDbDataReader(CommandBehavior behavior)\n  
   at Microsoft.EntityFrameworkCore.Storage.RelationalCommand.ExecuteReader(RelationalCommandParameterObject parameterObject)\n  
   at Microsoft.EntityFrameworkCore.Query.Internal.SingleQueryingEnumerable\u00601.Enumerator.InitializeReader(Enumerator enumerator)\n  
   at Microsoft.EntityFrameworkCore.Query.Internal.SingleQueryingEnumerable\u00601.Enumerator.\u003C\u003Ec.\u003CMoveNext\u003Eb__19_0(DbContext _, Enumerator enumerator)\n  
   at Microsoft.EntityFrameworkCore.SqlServer.Storage.Internal.SqlServerExecutionStrategy.Execute[TState,TResult](TState state, Func\u00603 operation, Func\u00603 verifySucceeded)\n  
   at Microsoft.EntityFrameworkCore.Query.Internal.SingleQueryingEnumerable\u00601.Enumerator.MoveNext()\nClientConnectionId:038757a9-2247-477d-98c5-31eacfa8d39d\nError Number:-2,State:0,Class:11",
  "Exception": "Microsoft.Data.SqlClient.SqlException (0x80131904): Execution Timeout Expired.  The timeout period elapsed prior to completion of the operation or the server is not responding.\n ---\u003E System.ComponentModel.Win32Exception (258): Unknown error 258\n  
   at Microsoft.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection, Action\u00601 wrapCloseInAction)\n  
   at Microsoft.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose)\n  
   at Microsoft.Data.SqlClient.TdsParserStateObject.ThrowExceptionAndWarning(Boolean callerHasConnectionLock, Boolean asyncClose)\n  
   at Microsoft.Data.SqlClient.TdsParserStateObject.ReadSniError(TdsParserStateObject stateObj, UInt32 error)\n  
   at Microsoft.Data.SqlClient.TdsParserStateObject.ReadSniSyncOverAsync()\n  
   at Microsoft.Data.SqlClient.TdsParserStateObject.TryReadNetworkPacket()\n  
   at Microsoft.Data.SqlClient.TdsParserStateObject.TryPrepareBuffer()\n  
   at Microsoft.Data.SqlClient.TdsParserStateObject.TryReadByte(Byte\u0026 value)\n  
   at Microsoft.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean\u0026 dataReady)\n  
   at Microsoft.Data.SqlClient.SqlDataReader.TryConsumeMetaData()\n  
   at Microsoft.Data.SqlClient.SqlDataReader.get_MetaData()\n  
   at Microsoft.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString, Boolean isInternal, Boolean forDescribeParameterEncryption, Boolean shouldCacheForAlwaysEncrypted)\n  
   at Microsoft.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean isAsync, Int32 timeout, Task\u0026 task, Boolean asyncWrite, Boolean inRetry, SqlDataReader ds, Boolean describeParameterEncryptionRequest)\n  
   at Microsoft.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, TaskCompletionSource\u00601 completion, Int32 timeout, Task\u0026 task, Boolean\u0026 usedCache, Boolean asyncWrite, Boolean inRetry, String method)\n  
   at Microsoft.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method)\n  
   at Microsoft.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior)\n  
   at Microsoft.Data.SqlClient.SqlCommand.ExecuteDbDataReader(CommandBehavior behavior)\n  
   at Microsoft.EntityFrameworkCore.Storage.RelationalCommand.ExecuteReader(RelationalCommandParameterObject parameterObject)\n  
   at Microsoft.EntityFrameworkCore.Query.Internal.SingleQueryingEnumerable\u00601.Enumerator.InitializeReader(Enumerator enumerator)\n  
   at Microsoft.EntityFrameworkCore.Query.Internal.SingleQueryingEnumerable\u00601.Enumerator.\u003C\u003Ec.\u003CMoveNext\u003Eb__19_0(DbContext _, Enumerator enumerator)\n  
   at Microsoft.EntityFrameworkCore.SqlServer.Storage.Internal.SqlServerExecutionStrategy.Execute[TState,TResult](TState state, Func\u00603 operation, Func\u00603 verifySucceeded)\n  
   at Microsoft.EntityFrameworkCore.Query.Internal.SingleQueryingEnumerable\u00601.Enumerator.MoveNext()\nClientConnectionId:038757a9-2247-477d-98c5-31eacfa8d39d\nError Number:-2,State:0,Class:11

我们还在服务器上运行了跟踪，我们可以看到登录发生，然后在一段时间后注销发生，在工作查询上，我们可以看到登录、查询，然后直接注销.

下面是一个由其中一个断开的查询生成的SQL示例，它是一个连接，但在99.99%的情况下，结果总是1比1

SELECT [t0].[id],
       [t0].[accountid],
       [t0].[amendedondateutc],
       [t0].[billrundate],
       [t0].[billingdate],
       [t0].[contractid],
       [t0].[corrolationid],
       [t0].[createdondateutc],
       [t0].[creditnotenumber],
       [t0].[deletedflag],
       [t0].[description],
       [t0].[invoicenumber],
       [t0].[invoicesent],
       [t0].[methodid],
       [t0].[ordernumber],
       [t0].[paid],
       [t0].[partyid],
       [t0].[status],
       [t0].[tenantid],
       [t0].[tokenid],
       [t1].[id],
       [t1].[amendedondateutc],
       [t1].[createdondateutc],
       [t1].[deletedflag],
       [t1].[description],
       [t1].[externalitemid],
       [t1].[invoicedescription],
       [t1].[note],
       [t1].[priceexcluding],
       [t1].[quantity],
       [t1].[transactiondate],
       [t1].[transactionid]
FROM   (SELECT TOP(1) [t].[id],
                      [t].[accountid],
                      [t].[amendedondateutc],
                      [t].[billrundate],
                      [t].[billingdate],
                      [t].[contractid],
                      [t].[corrolationid],
                      [t].[createdondateutc],
                      [t].[creditnotenumber],
                      [t].[deletedflag],
                      [t].[description],
                      [t].[invoicenumber],
                      [t].[invoicesent],
                      [t].[methodid],
                      [t].[ordernumber],
                      [t].[paid],
                      [t].[partyid],
                      [t].[status],
                      [t].[tenantid],
                      [t].[tokenid]
        FROM   [dbo].[transactions] AS [t]
        WHERE  [t].[id] = '*****') AS [t0]
       LEFT JOIN [dbo].[transactionitems] AS [t1]
              ON [t0].[id] = [t1].[transactionid]
ORDER  BY [t0].[id]