I'm experiencing problems with EF and SQL Server when running on Azure Webservice. The same code runs fine from VS with same db.

Question

If I turn debugging on and connect my VS to the process, the error doesn't occur. Also I can run the code just fine from Visual Studio towards the same database. I do not Lazy Load. All Queries ens with ToFirstAsync() or ToListAsync() and has a Select to specify what to get.

This is how we make the call, from a Razorcomponent that displays My active cases:

List<CaseModel> Model { get; set; } = [];

protected override async Task OnInitializedAsync()

{

Model = (await _caseService.GetMyActiveList()).ToList();

_isLoading = false;

}

And here is the method GetMyActiveList() :

public async Task<IEnumerable<CaseModel>> GetMyActiveList()

{

await using var ctx = _dbContextFactory.CreateDbContext();

var userId = _userProvider.UserId;

var returnvalue = await ctx.Cases

.Where(item => item.CaseHandlers.Any(ch => ch.ReCordUserId == userId))

.Select(entity => new CaseModel

{

Id = entity.Id,

RegisterId = entity.RegisterId,

CaseNumber = entity.CaseNumber,

CaseStatusId = entity.CaseStatusId,

CaseTitle = entity.CaseTitle,

CreatedByUserId = entity.CreatedByUserId,

ClassificationCodeId = entity.ClassificationCodeId,

CaseTypeId = entity.CaseTypeId,

Confidentiality = entity.Confidentiality,

CaseCreatedDate = entity.CaseCreatedDate,

ClassificationCodeName = entity.ClassificationCode!.Name,

CaseTypeName = entity.CaseType!.Name,

CaseStatusName = entity.CaseStatus!.Name,

RegisterName = entity.Register!.Name,

CreatedByUserName = entity.CreatedByUser!.UserName,

})

.ToListAsync();

return returnvalue;

}

Here is the error in the log on Azure:

2025-02-15 08:40:37.986 +00:00 [Error] Microsoft.EntityFrameworkCore.Database.Command: Failed executing DbCommand (23ms) [Parameters=[@__ef_filter__TenantId_0='?' (DbType = Int32), @__userId_0='?' (DbType = Int32)], CommandType='Text', CommandTimeout='30']

SELECT [c].[Id], [c].[RegisterId], [c].[CaseNumber], [c].[CaseStatusId], [c].[CaseTitle], [c].[CreatedByUserId], [c].[ClassificationCodeId], [c].[CaseTypeId], [c].[Confidentiality], [c].[CaseCreatedDate], [c2].[Name] AS [ClassificationCodeName], [c4].[Name] AS [CaseTypeName], [c6].[Name] AS [CaseStatusName], [r0].[Name] AS [RegisterName], [r1].[UserName] AS [CreatedByUserName]

FROM [Case] AS [c]

LEFT JOIN (

SELECT [c1].[Id], [c1].[Name]

FROM [ClassificationCode] AS [c1]

WHERE [c1].[TenantId] = @__ef_filter__TenantId_0

) AS [c2] ON [c].[ClassificationCodeId] = [c2].[Id]

INNER JOIN (

SELECT [c3].[Id], [c3].[Name]

FROM [CaseType] AS [c3]

WHERE [c3].[TenantId] = @__ef_filter__TenantId_0

) AS [c4] ON [c].[CaseTypeId] = [c4].[Id]

INNER JOIN (

SELECT [c5].[Id], [c5].[Name]

FROM [CaseStatus] AS [c5]

WHERE [c5].[TenantId] = @__ef_filter__TenantId_0

) AS [c6] ON [c].[CaseStatusId] = [c6].[Id]

INNER JOIN (

SELECT [r].[Id], [r].[Name]

FROM [Register] AS [r]

WHERE [r].[TenantId] = @__ef_filter__TenantId_0

) AS [r0] ON [c].[RegisterId] = [r0].[Id]

INNER JOIN [ReCordUser] AS [r1] ON [c].[CreatedByUserId] = [r1].[Id]

WHERE [c].[TenantId] = @__ef_filter__TenantId_0 AND EXISTS (

SELECT 1

FROM [CaseHandler] AS [c0]

WHERE [c0].[TenantId] = @__ef_filter__TenantId_0 AND [c].[Id] = [c0].[CaseId] AND [c0].[ReCordUserId] = @__userId_0)

2025-02-15 08:40:38.025 +00:00 [Error] Microsoft.EntityFrameworkCore.Query: An exception occurred while iterating over the results of a query for context type 'ReCord.Data.ApplicationDbContext'.

System.InvalidOperationException: Invalid operation. The connection is closed.

at Microsoft.Data.SqlClient.SqlCommand.<>c.<ExecuteDbDataReaderAsync>b__211_0(Task`1 result)

at System.Threading.Tasks.ContinuationResultTaskFromResultTask`2.InnerInvoke()

at System.Threading.Tasks.Task.<>c.<.cctor>b__292_0(Object obj)

at System.Threading.ExecutionContext.RunInternal(ExecutionContext executionContext, ContextCallback callback, Object state)

--- End of stack trace from previous location ---

at System.Threading.ExecutionContext.RunInternal(ExecutionContext executionContext, ContextCallback callback, Object state)

at System.Threading.Tasks.Task.ExecuteWithThreadLocal(Task& currentTaskSlot, Thread threadPoolThread)

--- End of stack trace from previous location ---

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.SingleQueryingEnumerable`1.AsyncEnumerator.InitializeReaderAsync(AsyncEnumerator enumerator, CancellationToken cancellationToken)

at Microsoft.EntityFrameworkCore.SqlServer.Storage.Internal.SqlServerExecutionStrategy.ExecuteAsyncTState,TResult

at Microsoft.EntityFrameworkCore.Query.Internal.SingleQueryingEnumerable`1.AsyncEnumerator.MoveNextAsync()

System.InvalidOperationException: Invalid operation. The connection is closed.

at Microsoft.Data.SqlClient.SqlCommand.<>c.<ExecuteDbDataReaderAsync>b__211_0(Task`1 result)

at System.Threading.Tasks.ContinuationResultTaskFromResultTask`2.InnerInvoke()

at System.Threading.Tasks.Task.<>c.<.cctor>b__292_0(Object obj)

at System.Threading.ExecutionContext.RunInternal(ExecutionContext executionContext, ContextCallback callback, Object state)

--- End of stack trace from previous location ---

at System.Threading.ExecutionContext.RunInternal(ExecutionContext executionContext, ContextCallback callback, Object state)

at System.Threading.Tasks.Task.ExecuteWithThreadLocal(Task& currentTaskSlot, Thread threadPoolThread)

--- End of stack trace from previous location ---

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.SingleQueryingEnumerable`1.AsyncEnumerator.InitializeReaderAsync(AsyncEnumerator enumerator, CancellationToken cancellationToken)

at Microsoft.EntityFrameworkCore.SqlServer.Storage.Internal.SqlServerExecutionStrategy.ExecuteAsyncTState,TResult

at Microsoft.EntityFrameworkCore.Query.Internal.SingleQueryingEnumerable`1.AsyncEnumerator.MoveNextAsync()

2025-02-15 08:40:38.026 +00:00 [Debug] Microsoft.EntityFrameworkCore.Infrastructure: 'ApplicationDbContext' disposed.

2025-02-15 08:40:38.044 +00:00 [Warning] Microsoft.AspNetCore.Components.Server.Circuits.RemoteRenderer: Unhandled exception rendering component: Invalid operation. The connection is closed.

System.InvalidOperationException: Invalid operation. The connection is closed.

at Microsoft.Data.SqlClient.SqlCommand.<>c.<ExecuteDbDataReaderAsync>b__211_0(Task`1 result)

at System.Threading.Tasks.ContinuationResultTaskFromResultTask`2.InnerInvoke()

at System.Threading.Tasks.Task.<>c.<.cctor>b__292_0(Object obj)

at System.Threading.ExecutionContext.RunInternal(ExecutionContext executionContext, ContextCallback callback, Object state)

--- End of stack trace from previous location ---

at System.Threading.ExecutionContext.RunInternal(ExecutionContext executionContext, ContextCallback callback, Object state)

at System.Threading.Tasks.Task.ExecuteWithThreadLocal(Task& currentTaskSlot, Thread threadPoolThread)

--- End of stack trace from previous location ---

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.SingleQueryingEnumerable`1.AsyncEnumerator.InitializeReaderAsync(AsyncEnumerator enumerator, CancellationToken cancellationToken)

at Microsoft.EntityFrameworkCore.SqlServer.Storage.Internal.SqlServerExecutionStrategy.ExecuteAsyncTState,TResult

at Microsoft.EntityFrameworkCore.Query.Internal.SingleQueryingEnumerable`1.AsyncEnumerator.MoveNextAsync()

at Microsoft.EntityFrameworkCore.EntityFrameworkQueryableExtensions.ToListAsyncTSource

at Microsoft.EntityFrameworkCore.EntityFrameworkQueryableExtensions.ToListAsyncTSource

at ReCord.Business.Service.CaseService.GetMyActiveList() in D:\a\1\s\ReCord.Business\Service\CaseService.cs:line 62

at ReCord.Business.Service.CaseService.GetMyActiveList() in D:\a\1\s\ReCord.Business\Service\CaseService.cs:line 83

at ReCord.Api.Mirror.Controllers.CaseService.GetMyActiveList() in D:\a\1\s\ReCord.Api.Mirror\Controllers\CaseService.cs:line 9

at ReCord.Web.Components.Pages.Case.MyCasesComponent.OnInitializedAsync() in D:\a\1\s\ReCord.Web\Components\Pages\Case\MyCasesComponent.razor:line 31

at Microsoft.AspNetCore.Components.ComponentBase.RunInitAndSetParametersAsync()

at Microsoft.AspNetCore.Components.RenderTree.Renderer.GetErrorHandledTask(Task taskToHandle, ComponentState owningComponentState)

2025-02-15 08:40:38.061 +00:00 [Error] Microsoft.AspNetCore.Components.Server.Circuits.CircuitHost: Unhandled exception in circuit 'ShbvMWyOeK0mftzyGS30vVdKruSyVIRicebcAyFdaTg'.

System.InvalidOperationException: Invalid operation. The connection is closed.

at Microsoft.Data.SqlClient.SqlCommand.<>c.<ExecuteDbDataReaderAsync>b__211_0(Task`1 result)

at System.Threading.Tasks.ContinuationResultTaskFromResultTask`2.InnerInvoke()

at System.Threading.Tasks.Task.<>c.<.cctor>b__292_0(Object obj)

at System.Threading.ExecutionContext.RunInternal(ExecutionContext executionContext, ContextCallback callback, Object state)

--- End of stack trace from previous location ---

at System.Threading.ExecutionContext.RunInternal(ExecutionContext executionContext, ContextCallback callback, Object state)

at System.Threading.Tasks.Task.ExecuteWithThreadLocal(Task& currentTaskSlot, Thread threadPoolThread)

--- End of stack trace from previous location ---

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.SingleQueryingEnumerable`1.AsyncEnumerator.InitializeReaderAsync(AsyncEnumerator enumerator, CancellationToken cancellationToken)

at Microsoft.EntityFrameworkCore.SqlServer.Storage.Internal.SqlServerExecutionStrategy.ExecuteAsyncTState,TResult

at Microsoft.EntityFrameworkCore.Query.Internal.SingleQueryingEnumerable`1.AsyncEnumerator.MoveNextAsync()

at Microsoft.EntityFrameworkCore.EntityFrameworkQueryableExtensions.ToListAsyncTSource

at Microsoft.EntityFrameworkCore.EntityFrameworkQueryableExtensions.ToListAsyncTSource

at ReCord.Business.Service.CaseService.GetMyActiveList() in D:\a\1\s\ReCord.Business\Service\CaseService.cs:line 62

at ReCord.Business.Service.CaseService.GetMyActiveList() in D:\a\1\s\ReCord.Business\Service\CaseService.cs:line 83

at ReCord.Api.Mirror.Controllers.CaseService.GetMyActiveList() in D:\a\1\s\ReCord.Api.Mirror\Controllers\CaseService.cs:line 9

at ReCord.Web.Components.Pages.Case.MyCasesComponent.OnInitializedAsync() in D:\a\1\s\ReCord.Web\Components\Pages\Case\MyCasesComponent.razor:line 31

at Microsoft.AspNetCore.Components.ComponentBase.RunInitAndSetParametersAsync()

at Microsoft.AspNetCore.Components.RenderTree.Renderer.GetErrorHandledTask(Task taskToHandle, ComponentState owningComponentState)

Answer 1

Hi Tomas Ekenman,

I am glad that you made it! but as you asked for access token authorization approach , so we referred your code itself and made some changes according to your requirement for authenticating your application to Azure SQL Database using Access Tokens (Azure Identity). hope that helps,

As your integration of AzureSqlTokenProvider in your AddDbContextFactory seems perfect, we made few changes on your tokenprovider as below:

public class AzureSqlTokenProvider

{

private string? _cachedToken;

private DateTime _tokenRenewalTime;

private readonly object _tokenLock = new object();

public string GetAccessToken()

{

    try

    {

        // Check if the token is expired or null and needs renewal

        if (_cachedToken == null || DateTime.UtcNow >= _tokenRenewalTime)

        {

            lock (_tokenLock) // Ensure thread safety during token renewal

            {

                // Double-check the token status inside the lock (because other threads may have renewed it)

                if (_cachedToken == null || DateTime.UtcNow >= _tokenRenewalTime)

                {

                    var credential = new DefaultAzureCredential();

                    var tokenRequestContext = new Azure.Core.TokenRequestContext(new[] { "https://database.windows.net/.default" });


                    

                    // Get the token

                    var accessToken = credential.GetToken(tokenRequestContext);

                    // Cache the token and its expiration time

                    _cachedToken = accessToken.Token;

                    var expires = accessToken.ExpiresOn.UtcDateTime;

                    // Set the token renewal time to 1 minute before expiration

                    _tokenRenewalTime = expires.AddMinutes(-1);

                }

            }

        }

    }

    catch (Exception ex)

    {

        // Log the exception (you could replace Console.WriteLine with your own logging mechanism)

        Console.WriteLine($"Error acquiring token: {ex.Message}");

        throw;  // Optionally, you may want to rethrow or handle the exception appropriately

    }

    // Return the cached token

    return _cachedToken;

}

}

notes:

We have ensure that only one thread requests and caches the token at a time.

Checked for token expiry and renews it before it expires (with a 1-minute buffer).

Catches and logs errors during token acquisition, providing better diagnostics.

If you have any further assistant, do let me know.

If the answer is helpful, please click Accept Answer and kindly upvote it so that other people who faces similar issue may get benefitted from it.

Answer 2

After weeks of frustration (the problem moved to different functions every release and dissappeared somtimes) it is solved. Or I think it is:

In our azure-environments we use AccessTokens to authorize the application against the SQL Server like this:
services.AddDbContextFactory<ApplicationDbContext>((serviceProvider, options) =>

{

var tokenProvider = serviceProvider.GetRequiredService<AzureSqlTokenProvider>();

options.UseSqlServer(new SqlConnection(connectionString)

{

AccessToken = tokenProvider.GetAccessToken(),

}, sqlServerOptions =>

{

sqlServerOptions.EnableRetryOnFailure(

maxRetryCount: 2,

maxRetryDelay: TimeSpan.FromSeconds(10),

errorNumbersToAdd: null);

});

});

For reference, out tokenprovider looks like this:

public class AzureSqlTokenProvider

{

private string? _cachedToken;

private DateTime _tokenRenewalTime;

public string GetAccessToken()

{

if (_cachedToken == null || DateTime.UtcNow >= _tokenRenewalTime)

{

var credential = new DefaultAzureCredential();

var tokenRequestContext = new Azure.Core.TokenRequestContext(["https://database.windows.net/.default"]);

var accessToken = credential.GetToken(tokenRequestContext);

_cachedToken = accessToken.Token;

var expires = accessToken.ExpiresOn.UtcDateTime;

_tokenRenewalTime = expires.AddMinutes(-1);

}

return _cachedToken;

}

}
If we use username and password, the issue does not appear so we are switching.

If someone can see what is wrong with our code using tokens to authorize, we should still prefer this way,

Answer 3

Hi Tomas Ekenman,

In addition to AgaveJoe response,

To fix the issue, Lets follow these steps below:

The key error here is: "System.InvalidOperationException: Invalid operation. The connection is closed."

1. Azure’s SQL Server may be closing idle connections. When you're running locally with debugging, the connection might be kept alive, but it could be closing on Azure due to timeout or pooling behavior. You can try to adjust connection string parameters related to pooling and timeouts. In your connection string, ensure that you have something like: Pooling=True;Max Pool Size=100;Min Pool Size=10;Connect Timeout=30;.
2. From the error log, it seems like the DbContext is disposed prematurely. Double-check that you're managing your DbContext's lifecycle properly. The issue might happen if your DbContext is disposed before all queries are complete.Instead of using 'await using' for the context, try explicitly creating and disposing it within the method to ensure the lifecycle is controlled properly.
3. Entity Framework Core Timeout or Connection Limit- If the application is making multiple requests in parallel or experiencing high traffic on Azure, SQL Server might hit connection limits or timeouts. You can adjust the command timeout and retry strategies.
4. Investigate Connection Lifecycle in Azure- On Azure, if there are multiple instances of your app or scaling issues, it may be causing multiple connections to the database to be opened and closed rapidly. You may want to check if your app is running in multiple instances or if Azure is scaling it unexpectedly.
5. Since this happens only on Azure and not locally, it could be related to the network connection between your Azure web app and the database. You might want to check the Azure SQL database firewall settings and ensure there are no intermittent connectivity issues or throttling on the database.
6. Since you can replicate the issue in Azure, ensure that your logging captures the full error details, especially around connection management. You might be able to see more details about the failure before the connection closes if you add more logging around your database context.

If you have any further assistant, do let me know.If the answer is helpful, please click Accept Answer and kindly upvote it so that other people who faces similar issue may get benefitted from it.