Configuring Entity Framework Core with Dynamic Connection Strings – ASP.NET Core

We need to handle dynamic connection strings. The idea is that different clients of our Web API need to have different databases. We want that when client A makes a request to the Web API, database X is used; and when a client B does the same, then database Z is used.

One reason why we might want this is if we have a multi-tenancy environment and we want our clients to have individual databases. It is not mandatory to have individual databases in multi-tenancy environments, but it is a requirement that some companies may have.

Solution

When we are configuring the DbContext in an ASP.NET Core web application, we typically use AddDbContext as follows:

Notice that as an AddDbContext parameter we have an Action. With an Action we encapsulate a method. In this case, the Action must construct the DbContext options. What interests us most is building the connection string according to the client making the HTTP request, say, the client sends us a ‘tenantId’, but where will we get this information from?

We can get this data wherever we want:

  • URL
  • A field in the HTTP header
  • A cookie

I am going to pretend that such information comes from a field in the HTTP header.

The second thing we need is a way to build a connection string. In my case, I am going to have a connection string template which is going to point to a certain server, but the name of the database is going to be the tenantId. For this in my appsettings.json I put the following:

Notice that in Initial Catalog we have {dbName} which is where we will dynamically place the name of the database. You could also make the Data Source dynamic if you want.

Note: Another solution would be to use the SqlConnectionStringBuilder class to dynamically construct the connection string.

Then we need the HTTP header inside the AddDbContext. For this, we are going to use an overload that gives us an IServiceProvider, in this way, we will obtain the HttpContext, and thus, the desired header. Suppose the name of the header is TenantId. Therefore, the following code does the job:

As you can see, after getting the connection string template, we search for the database name which is equal to the value of the tenantId header, then we do the replacement, and finally we configure the DbContext with the connection string.

In this way we have configured EF Core with a dynamic connection string.

Security

You should not simply allow the user to send a tenantId with any value, since this would allow them to access the data of any client. One solution is to create an application-level policy that verifies that the user’s request is valid. This can be done by verifying that the user’s token (if you use JWT) contains the proper tenantId.

Courses

If you want to learn more about developing Web APIs with ASP.NET Core, get my course on Udemy today: https://www.udemy.com/course/building-restful-web-apis-with-aspnet-core/?referralCode=DAFD27F4028D04B62181

Learn how to develop interactive web applications with C# using Blazor: https://www.udemy.com/course/programming-in-blazor-aspnet-core/?referralCode=8EFA9D9FF38E3065DF0C

Regards!

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s