EF Core DB First, and how to avoid Constructor Overwrite on Model Generation

user_149689 0
Viewed 382

I am moving away from using SQL Authentication with my Azure DB, to Active Directory Managed Authentication as explained in this article.

Basically, I'm doing two main things to get this working.

1- injecting the token in the DBContext constructor:

public MyDBContext(DbContextOptions<MyDBContext> options)
    : base(options)
{
    var conn = (SqlConnection)Database.GetDbConnection();
    conn.AccessToken = (new Microsoft.Azure.Services.AppAuthentication.AzureServiceTokenProvider()).GetAccessTokenAsync("https://database.windows.net/").Result;

}

2- In My Web App Startup file, I'm injecting the DBContext

    string SqlConnection = localConfig["SqlConnectionString"];
    services.AddDbContext<MyDBContext>(options => options.UseSqlServer(SqlConnection, sqlServerOptions => { sqlServerOptions.CommandTimeout(1000); }));

My problem now is that every time I need to refresh the model using the Scaffold-DbContext command, my MyDbContext gets overwritten, and I lose the changes I've done to the constructor.

What solutions are possible to avoid this problem? OR, is there a better way to inject the Token somewhere else efficiently?

Edit: Please note that I am using EF 2.x

entity-framework-core azure-active-directory azure entity-framework c#
2 Answers

I've used an interceptor to inject access tokens:

public class ManagedIdentityConnectionInterceptor : DbConnectionInterceptor
{
    private readonly bool _useManagedIdentity;
    private readonly AzureServiceTokenProvider _tokenProvider;

    public ManagedIdentityConnectionInterceptor(bool useManagedIdentity)
    {
        _useManagedIdentity = useManagedIdentity;
        _tokenProvider = new AzureServiceTokenProvider();
    }

    public override async Task<InterceptionResult> ConnectionOpeningAsync(
        DbConnection connection,
        ConnectionEventData eventData,
        InterceptionResult result,
        CancellationToken cancellationToken = default)
    {
        if (_useManagedIdentity)
        {
            // In Azure, get an access token for the connection
            var sqlConnection = (SqlConnection)connection;
            var accessToken = await GetAccessTokenAsync(cancellationToken);
            sqlConnection.AccessToken = accessToken;
        }

        return result;
    }

    private async Task<string> GetAccessTokenAsync(CancellationToken cancellationToken)
    {
        // Get access token for Azure SQL DB
        var resource = "https://database.windows.net/";
        return await _tokenProvider.GetAccessTokenAsync(resource, cancellationToken: cancellationToken);
    }
}

Which can be added like this:

// Detect environment somehow (locally you might not want to use tokens)
var useManagedIdentity = true;
var managedIdentityInterceptor = new ManagedIdentityConnectionInterceptor(useManagedIdentity);
services.AddDbContext<Context>(options => options.UseSqlServer(connectionString).AddInterceptors(managedIdentityInterceptor));

This way no changes are needed in the constructor. The interceptor will get a token before a connection is opened to the SQL DB. Also we avoid doing sync-over-async in the constructor. Do note this requires EF Core 3.x.

user_16589060

You can use the UseSqlServer overload with DbConnection parameter and pass configured SqlConnection object:

var sqlConnectionString = localConfig["SqlConnectionString"];
services.AddDbContext<MyDBContext>(
    options => options.UseSqlServer(new SqlConnection(sqlConnectionString)
    {
        AccessToken = (new Microsoft.Azure.Services.AppAuthentication.AzureServiceTokenProvider()).GetAccessTokenAsync("https://database.windows.net/").Result
    },
    sqlServerOptions => { sqlServerOptions.CommandTimeout(1000); }));
user_52025630
Related