Update - READ THIS FIRST

There is now support to connect to any Azure SQL database, Azure SQL Managed Instance and Synapse SQL in Azure Synapse Analytics from your .NET Core app using just the connection string. To learn more about this read the following pages:

This will completely eliminate the need to write any code to obtain a service connection in your application. I found this out when I shared this post earlier on Twitter.

I am yet to try this out and will update this post as I do. But I thought, I would put this update up here so that you don’t waste your time doing what I did below.

That’s the beauty of the internet. You think you have learned something new and then someone gives you a better insight into it!

Background

I have mostly worked on projects that I inherited from others. This project inheritance way, is the most common way of starting your job at most companies. Unless you are part of the team or a startup building something new. But a vast majority of advertised jobs are for projects that were developed by those who may no longer be with that firm. This may not sound very satisfying or pleasant.

This can be exciting and daunting at the same time when you inherit an application from a team that probably no longer exists or only has a few of the original coders working in the organisation. You often find, code that you might consider bad. But without which, the company might not have made the revenue it did over the years.

The point is, software engineering is a craft.

However, one does not have to write the perfect code to create a usable product. We create an application for users, not just for coders to keep coding. So a little bit of imperfect code, sometimes goes a long way.

That said, one must make every effort to make things better as and when they can. But take it easy, refactoring code to perfection is not a sprint, it is a marathon and a pretty long one.

I have made my own share of mistakes and also complained about code that I inherited from others. I personally love perfecting what I have written. But developers must know when to stop.

This post is about, a code smell I encountered in a repository I inherited. The code in question:

public DataContext(DbContextOptions<DataContext> options ) : base(options)
{
    var conn = (Microsoft.Data.SqlClient.SqlConnection)Database.GetDbConnection();
    conn.AccessToken = (new Microsoft.Azure.Services.AppAuthentication.AzureServiceTokenProvider()).GetAccessTokenAsync("https://database.windows.net/", tenantId: "your-super-long-tenant-guid").Result;
}

What is wrong here?

  • The code above is meant to create a DataContext. It is the constructor of the context that is trying to also contact a service to retrieve an access token! That is too much work in a constructor! A constructor is only meant to initialise an instance of a class. Not to obtain connections where one has to actually make a call to an external service! I will explain more about Object Relational Mapping later.

  • The 4th line of the code, does something like (new Microsoft.Azure.Services.AppAuthentication.AzureServiceTokenProvider()).GetAccessTokenAsync("https://database.windows.net/", tenantId: "your-super-long-tenant-guid").Result;. This is a code smell or maybe a symptom of not understanding asynchronous programming in C# or a symptom of not having the time to make it better, because deadlines have to be met.

Object Relational Mapping

For those who do not know, or are not familiar with an object relational mapping:

It is a programming method that abstracts your code from the data store behind it.

The ORM gives you an interface to interact with any datastore behind the scene in such a way that your application would interact with the data as it were a collection of objects that may or may not be related to other objects in the same context.

A Context, in this sense, would be a collection of related data objects, or tables in the database. Most projects start with one and sometimes live with the same forever. But good project often identify contexts and break them down to smaller manage-able contexts.

ORMs do a lot of the work for you, but if you use one, you must understand how it works to use it effectively and efficiently and avoid any performance overhead.

For .NET, Entity Framework is a popular ORM created by the team at Microsoft.

Async and await in CSharp

This is a much detailed topic and I think I must write a separate post on the details. But I will try to summarise it to its essence over here. This is going to be hard for me, but if I don’t try, I will never know how this simplification can ever be done. Let us find out.

The bit of code that you see (new Microsoft.Azure.Services.AppAuthentication.AzureServiceTokenProvider()).GetAccessTokenAsync("https://database.windows.net/", tenantId: "your-super-long-tenant-guid").Result; is enforcing the running application to block all work until a response is received from the GetAccessTokenAsync method. What could have been awaited on is now being force to stop until further notice. Did not make sense? Let me try again.

await keyword in C# is an operation on the task. The asynchronous operation is the Task. In the snippet above, GetAccessTokenAsync is the asynchronous operation, which is a Task. Using the await keyword, tells the runtime that “Hey, this is going to take a couple of milliseconds. We can park this execution context here and go burn the CPU on something more pressing and get back to this context, when the Task returns”.

Talking to me?

You might now wonder why the runtime is speaking perfect English. Well, it was just a simplified explanation of kind of what happens behind the scenes. The runtime does not do the talking. Enough making fun of me. Please use your imagination here, I’m trying to explain something complex (maybe too simple for you), in simple words. Now that we have clarified your confusion, let us move on to the question of the .Result part of the line.

When the execution runtime encounters Task.Result, what happens is that, it doesn’t tell anyone, anything. All it does is, it just waits there for a result. There is no switching context for CPU to do more important or pressing work,

Stop. Wait!
Just stop and wait.

Like we all waited for 2020 and the pandemic to end and dreamt for a day when we could all go out to the pub together and grab a drink or have a meal together while talking about building applications with async and await.

When it is over...

So how do we fix this?

A better way to get access tokens for your Database Context

So if you think about, you probably already know what you have to fix now. Remove the access token retrieval from the constructor. Great! But then when, where and how do you acquire a connection in the first place?

Introducing Database Connection Interceptor

Before I get into the details of the DbConnectionInterceptor, I must first, introduce you to the concept of Interceptors in Entity Framework Core.

Sounds good to me...
Interceptors give you some super powers that you didn’t know existed before!
Ooh! I can create fire!
Well, this is not the kind of super power you would see in your favourite DC or Marvel movie. This is very specific to Entity Framework, of course. Sorry about the hype. Take it easy.

What are interceptors?

It gives you the following abilities on EF Core Operations:

  • interception - isn’t that just the name of this thing?
  • modification - modify what though?
  • suppression - sounds a bit sad to me

If you have been coding in OO languages for a while, you might have guessed by now that in order to create an interceptor, you have to implement a certain interface.

Damn right you are!
In this case, you have to implement one or more interfaces derived from IInterceptor and be registered only once in the application for any DbContext.

The types of Interceptors

Three Types

EF Core team, has provided us different categories of interceptors:

Oh but before you start implementing them directly, EF Core team has also provided us with the following base classes for convenience:

These classes contain no-op implementations for each method in the corresponding interface. So if you don’t want to implement all the methods in the interface, then deriving your implementation from the base class is a good idea. Saves you a great deal of time.

When do I intercept?

When?
Interception can be done before or after a certain operation. So you would generally find a pair of methods following the naming convention:

  • NounVerbPresentContinuousTense
  • NounVerbPastTense

Example of methods in DbCommandInterceptor

  • ReaderExecuting, which is called before a query is executed
  • ReaderExecuted which is called after a query has been sent to the database

Each of them come with their corresponding async methods too. So remember to implement both.

If you only implement the async version of the method and not the synchronous method, then you’ll quickly realise that your interception does not work when invoking the synchronous interactions with the Database, like dbContext.MyEntities.First(). This can become a pretty big problem if you were specifically dealing with obtaining connections.

Imagine only having implemented the access token retrieval in the Async version of the interceptor methods! You realise that you do not have a connection when you execute a synchronous EF Core method! Learned it the hard way, so I thought I would give it special mention here.

So that is a good introductory overview of interceptors. You can read more on Microsoft Docs. So I am now going to jump back to the interceptor that was going to solve our connection issue.

The DbConnectionInterceptor and SqlConnection’s AccessToken

I only just realised that this method was actually the one suggested in the official docs. I didn’t know this was the case, until I came across this after the original github issue where I first read about interceptors, mentioned in the invaluable links section.

However, this one is a slight variation. Tweak this according to your needs.

public class AadAuthenticationDbConnectionInterceptor : DbConnectionInterceptor
{
    private readonly Guid _tenantId;
    public AadAuthenticationDbConnectionInterceptor(Guid tenantId) => _tenantId = tenantId;
    
    public override InterceptionResult ConnectionOpening(
    DbConnection connection,
    ConnectionEventData eventData,
    InterceptionResult result) {
        return ConnectionOpeningAsync(connection, eventData, result, new CancellationTokenSource().token).GetAwaiter().GetResult();
    }
    
    public override async Task<InterceptionResult> ConnectionOpeningAsync(
        DbConnection connection,
        ConnectionEventData eventData,
        InterceptionResult result,
        CancellationToken cancellationToken = default)
    {
        var sqlConnection = (SqlConnection)connection;
        var connectionStringBuilder = new SqlConnectionStringBuilder(sqlConnection.ConnectionString);
        // if you are certain that the only way to access the the database connection is using a service token, you may 
        // remove this conditional retrieval of access token. 
        if (connectionStringBuilder.DataSource.Contains("database.windows.net", StringComparison.OrdinalIgnoreCase) && 
            string.IsNullOrEmpty(connectionStringBuilder.UserID))
        {
            sqlConnection.AccessToken = await GetAzureSqlAccessToken(cancellationToken);
        }
        return await base.ConnectionOpeningAsync(connection, eventData, result, cancellationToken);
    }
    private Task<string> GetAzureSqlAccessToken(CancellationToken cancellationToken)
    {
        var tokenProvider = new AzureServiceTokenProvider();
        return tokenProvider.GetAccessTokenAsync(
                resource: "https://database.windows.net/",
                tenantId: _tenantId.ToString(),
                cancellationToken);
    }
}

Notice that in my interceptor I have redirected the call to the async version from the synchronous ConnectionOpening method but with .GetAwaiter().GetResult(). This is not good practice, but for now, I have no other way to obtain a database connection. This is bad because there is no non-async method to obtain the access token and there is no universal and simple way to call an async method from non-async context without risking deadlock.. This is a really useful article and would help clarify a lot of your questions about async/await related concepts. Checkout MSDN Magazine Archive - July 2015 - Async programming - Brownfield Async Development.

More about Azure App Authentication client library - https://docs.microsoft.com/en-us/dotnet/api/overview/azure/service-to-service-authentication

Now that you have the DbConnectionInterceptor implemented in a way to acquire an access token synchronously and asynchronously, let us jump into the DbContext that uses this and other configuration you might have to do.

Registering Interceptors

Like most things in programming, there is more than one way to do the same thing.

Registering an Interceptor in DbContext.OnConfiguring

Interceptors can be registered in the OnConfiguring method of the DbContext. This is the prescribed method to override if you need to do some configuration, In fact, it takes in a parameter of type DbContextOptionsBuilder, so that you can call some convenience extension methods with corresponding params to do the configuration.

In this context here, the example below is based on the one from the official docs, not the same though, just wanted to give you an idea of how you could have configured the Azure AD Authentication Interceptor with your DbContext using the OnConfiguring method alone.

public class MyCoreContext : DbContext
{
    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
        => optionsBuilder.AddInterceptors(new AadAuthenticationDbConnectionInterceptor());
}

Although this is one way of doing it, I prefer another one.

Registering an Interceptor in Startup.cs

This approach is pretty simple and straightforward and will stay with your Dependency Injection configuration in the Startup file, which I feel makes the interceptors more obvious to the readers. So while registering you DbContext, you also register the Interceptors. They still only get invoked when they are supposed to. This is just the registration part.

    services
        .AddDbContext<DataContext>(options => options
        .UseSqlServer(Configuration.GetConnectionString("DefaultConnection"))
        .AddInterceptors(new AadAuthenticationDbConnectionInterceptor(_tenantId)));

In this case, my DbContext’s OnConfiguring method would look like:

public class DataContext : DbContext, IDataContext
{
    // other code not included to simplify ....
    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
    {
        if (!optionsBuilder.IsConfigured)
        {
            optionsBuilder.UseSqlServer("give a hardcoded conn string");
        }
        base.OnConfiguring(optionsBuilder);
    }
}

Invaluable References