Efcore: AddInterceptors not being called after second call to FromSqlRaw

[eduebrasil]

I'm using a generic repository implementation to handle my database operations. I have different requirements for authentication so for production we rely on Azure managed identities. To get the token I'm using an interceptor by calling

    options.UseSqlServer(configuration.GetConnectionString("MyConnectionString"));
    options.AddInterceptors(serviceProvider.GetRequiredService<AADTokenDbConnectionInterceptor>());

I'm tried both with AddDbContext and AddDbContextPool. Same result.

My implementation for AADTokenDbConnectionInterceptor sets the accessToken. The interceptor is registered as a singleton.

public override async Task<InterceptionResult> ConnectionOpeningAsync(DbConnection connection, ConnectionEventData eventData, InterceptionResult result, CancellationToken cancellationToken = default)
{
    var accessToken = await serviceTokenProvider
        .GetAccessTokenAsync("https://database.windows.net/", configuration.GetValue<string>("Authentication:Microsoft:TenantId"));

    var sqlConnection = (SqlConnection)connection;

    sqlConnection.AccessToken = accessToken;

    return result;
}

For normal entity framework core stuff, this works just fine. The problem is with the Raw Queries.
I'm executing a query using dbSet.FromSqlRaw(...).ToList() inside my repository which is registered as scoped.

In the first request, the query is executed. Even multiple times. All good.

When I try a second request, I get an error saying my credentials are wrong. Yes, they are because they were not supplied. The interceptor was not called. Calls for the first request only then stops working for the raw query.

Exception:

"exceptionType": "Microsoft.Data.SqlClient.SqlException",
  "stackTrace": "   at Microsoft.Data.ProviderBase.DbConnectionPool.CheckPoolBlockingPeriod(Exception e)\r\n   at Microsoft.Data.ProviderBase.DbConnectionPool.CreateObject(DbConnection owningObject, DbConnectionOptions userOptions, DbConnectionInternal oldConnection)\r\n   at Microsoft.Data.ProviderBase.DbConnectionPool.UserCreateRequest(DbConnection owningObject, DbConnectionOptions userOptions, DbConnectionInternal oldConnection)\r\n   at Microsoft.Data.ProviderBase.DbConnectionPool.TryGetConnection(DbConnection owningObject, UInt32 waitForMultipleObjectsTimeout, Boolean allowCreate, Boolean onlyOneCheckConnection, DbConnectionOptions userOptions, DbConnectionInternal& connection)\r\n   at Microsoft.Data.ProviderBase.DbConnectionPool.TryGetConnection(DbConnection owningObject, TaskCompletionSource`1 retry, DbConnectionOptions userOptions, DbConnectionInternal& connection)\r\n   at Microsoft.Data.ProviderBase.DbConnectionFactory.TryGetConnection(DbConnection owningConnection, TaskCompletionSource`1 retry, DbConnectionOptions userOptions, DbConnectionInternal oldConnection, DbConnectionInternal& connection)\r\n   at Microsoft.Data.ProviderBase.DbConnectionInternal.TryOpenConnectionInternal(DbConnection outerConnection, DbConnectionFactory connectionFactory, TaskCompletionSource`1 retry, DbConnectionOptions userOptions)\r\n   at Microsoft.Data.ProviderBase.DbConnectionClosed.TryOpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory, TaskCompletionSource`1 retry, DbConnectionOptions userOptions)\r\n   at Microsoft.Data.SqlClient.SqlConnection.TryOpen(TaskCompletionSource`1 retry)\r\n   at Microsoft.Data.SqlClient.SqlConnection.Open()\r\n   at Microsoft.EntityFrameworkCore.Storage.RelationalConnection.OpenDbConnection(Boolean errorsExpected)\r\n   at Microsoft.EntityFrameworkCore.Storage.RelationalConnection.Open(Boolean errorsExpected)\r\n   at Microsoft.EntityFrameworkCore.Storage.RelationalCommand.ExecuteReader(RelationalCommandParameterObject parameterObject)\r\n   at Microsoft.EntityFrameworkCore.Query.Internal.QueryingEnumerable`1.Enumerator.InitializeReader(DbContext _, Boolean result)\r\n   at Microsoft.EntityFrameworkCore.SqlServer.Storage.Internal.SqlServerExecutionStrategy.Execute[TState,TResult](TState state, Func`3 operation, Func`3 verifySucceeded)\r\n   at Microsoft.EntityFrameworkCore.Query.Internal.QueryingEnumerable`1.Enumerator.MoveNext()\r\n   at System.Collections.Generic.List`1..ctor(IEnumerable`1 collection)\r\n   at System.Linq.Enumerable.ToList[TSource](IEnumerable`1 source)\r\n   at Hub.Persistence.Repositories.Inventory.InventoryItemStatusRepository.GetLatestInventoryItemStatus(Int32 inventoryStatusId) in C:\\Development\\Poatek\\Hub\\Hub.Persistence\\Repositories\\Inventory\\InventoryItemStatusRepository.cs:line 20\r\n   at Hub.Application.Inventory.InventoryItemStore.GetInventoryDashboardAsync() in C:\\Development\\Poatek\\Hub\\Hub.Application\\Inventory\\InventoryItemStore.cs:line 115\r\n   at Hub.Web.Controllers.InventoryController.GetInventoryDashboardAsync() in C:\\Development\\Poatek\\Hub\\Hub.Api\\Controllers\\Inventory\\InventoryController.cs:line 59\r\n   at Microsoft.AspNetCore.Mvc.Infrastructure.ActionMethodExecutor.TaskOfIActionResultExecutor.Execute(IActionResultTypeMapper mapper, ObjectMethodExecutor executor, Object controller, Object[] arguments)\r\n   at Microsoft.AspNetCore.Mvc.Infrastructure.ControllerActionInvoker.<InvokeActionMethodAsync>g__Awaited|12_0(ControllerActionInvoker invoker, ValueTask`1 actionResultValueTask)\r\n   at Microsoft.AspNetCore.Mvc.Infrastructure.ControllerActionInvoker.<InvokeNextActionFilterAsync>g__Awaited|10_0(ControllerActionInvoker invoker, Task lastTask, State next, Scope scope, Object state, Boolean isCompleted)\r\n   at Microsoft.AspNetCore.Mvc.Infrastructure.ControllerActionInvoker.Rethrow(ActionExecutedContextSealed context)\r\n   at Microsoft.AspNetCore.Mvc.Infrastructure.ControllerActionInvoker.Next(State& next, Scope& scope, Object& state, Boolean& isCompleted)\r\n   at Microsoft.AspNetCore.Mvc.Infrastructure.ControllerActionInvoker.InvokeInnerFilterAsync()\r\n--- End of stack trace from previous location where exception was thrown ---\r\n   at Microsoft.AspNetCore.Mvc.Infrastructure.ResourceInvoker.<InvokeNextExceptionFilterAsync>g__Awaited|25_0(ResourceInvoker invoker, Task lastTask, State next, Scope scope, Object state, Boolean isCompleted)",

The same error happens when running locally pointing to AzureDB or running directly on Azure App Service.

EF Core version: 3.1.5
Database provider: (e.g. Microsoft.EntityFrameworkCore.SqlServer)
Target framework: (e.g. .NET Core 3.1)
Operating system: Windows 10
IDE: Visual Studio 2019 16.7.1

[smitpatel]

@eduebrasil - Please share code of your repository in which FromSqlRaw method is used.

[eduebrasil]

@smitpatel

public class InventoryItemStatusRepository : Repository<InventoryItemStatus>, IInventoryItemStatusRepository
{
    public InventoryItemStatusRepository(MyContext context) : base(context)
    {
    }

    public IEnumerable<InventoryItemStatus> GetLatestInventoryItemStatus(int inventoryStatusId)
    {
        var param = new SqlParameter("@InventoryStatusId", inventoryStatusId);
        var items = dbSet
            .FromSqlRaw(
                @"query using CTE"
            )
            .ToList();

        return items;
    }
}

The base repository looks like this:

public class Repository<TEntity> : IRepository<TEntity> where TEntity : class
{
    protected readonly MyContext context;
    protected readonly DbSet<TEntity> dbSet;

    public Repository(MyContext  context)
    {
        this.context = context;
        dbSet = context.Set<TEntity>();
    }

    public void Add(TEntity entity)
    {
        dbSet.Add(entity);
    }

    public void AddRange(IEnumerable<TEntity> entities)
    {
        dbSet.AddRange(entities);
    }

    public void Update(TEntity entity)
    {
        dbSet.Update(entity);
    }

    public void Remove(TEntity entity)
    {
        dbSet.Remove(entity);
    }

    public void RemoveRange(IEnumerable<TEntity> entities)
    {
        dbSet.RemoveRange(entities);
    }

    public void Attach<T>(T entity)
        where T : class
    {
        context.Attach(entity);
    }

    public void Detach<T>(T entity)
    where T : class
    {
        context.Entry(entity).State = EntityState.Detached;
    }

    internal DbSet<TEntity> GetDbSet() => dbSet;
}

[smitpatel]

@eduebrasil - At this point we will need full repro code unless @ajcvickers knows if there is something obviously wrong.

For normal entity framework core stuff, this works just fine. The problem is with the Raw Queries.

Can you elaborate on this? FromSqlRaw seems red herring, it should happen with any query since the query is not depending directly on anything. Does any other query works fine? Does any other operation which requires connection to database works fine across requests?

Query/Repository is scoped but interceptor is singleton. So when a new request starts its own scope, how is the interceptor supposed to receive services from that scope? If what interceptor is doing actually changes across requests.

Did you verify that interceptor is not being invoked or it is just error that authentication is not set correctly? Auth error can also happen even if interceptor is called if interceptor is not getting right values to set.

[ajcvickers]

I do notice that FromSqlRaw is being evaluated with ToList, which is not async, and so will never call ConnectionOpeningAsync. You can either make the query async or implement both ConnectionOpeningAsync and ConnectionOpening.

[eduebrasil]

@ajcvickers you are 100% correct. I missed that.
Changed the code here and it works as expected.

Closing this. Thanks @ajcvickers and @smitpatel for looking at this so quickly.