Implementing multi-tenancy in ASP.Net: Setting up Database Contexts

Implementing multi-tenancy in ASP.Net: Setting up Database Contexts

In the blog post, I walk you through how to create the database context. You can find the code on this repo.

Here are all the articles

1. Implementing multi-tenancy in ASP.Net
2. Implementing multi-tenancy in ASP.Net: Setting up Database Contexts
3. Implementing multi-tenancy in ASP.Net: Resolving the tenant
4. Implementing multi-tenancy in ASP.Net: Middleware

Setting up the project

In this example, I use the GUI to create a .net 7 project. Please note that for simplicity, I will be creating one project, in one solution. Ideally, you’d need to have a API, Core, Infrastructure, etc. projects depending on your requirements. I will create folders for these.

I will also not be using the repository pattern and CQRS at this point. I may decide to do so in the future.

Configure your new project

Additional information

Installing nuget packages

For this blog, we will need to install the following nuget packages and make the necessary configurations:

1. Microsoft.EntityFrameworkCore: This is the core Entity Framework package required to work with databases using DbContext and LINQ.
2. Microsoft.EntityFrameworkCore.Design: Needed for Entity Framework Core design-time operations, such as migrations. This package is a prerequisite for using the dotnet ef commands.
3. Microsoft.EntityFrameworkCore.SqlServer: This package allows Entity Framework Core to be used with Microsoft SQL Server.
4. Microsoft.Extensions.Configuration: Provides the necessary APIs for working with configuration data, such as reading from appsettings.json and environment variables.
5. Microsoft.Extensions.Configuration.Json: Allows the application to read configuration settings from JSON files, such as appsettings.json.
6. Microsoft.Extensions.Configuration.EnvironmentVariables: Enables configuration settings to be read from environment variables, which is especially useful for overriding settings in different deployment environments.
7. Microsoft.Extensions.Options: Enables the use of IOptions pattern, which is a way to access configuration values in a strongly-typed manner within your application.
8. AutoMapper: A library that simplifies the mapping between different object models. It eliminates the need for manual mapping code, making it easier to move data between types.

Installed nugget packages

Defining the DbContexts

Since we are not creating multiple projects, we will be using folders to organise our code. Create a “Data” folder in the project and a two more folders, EntityConfigurations and Factories.

You can read more about entity configurations and the single responsibility principle in this blog post. You may need to read it to understand the line: modelBuilder.ApplyConfigurationsFromAssembly(typeof(PlatformDbContext).Assembly);.

In the Data folder create the PlatformDbContext and the TenantDbContext as follows:

public class PlatformDbContext : DbContext
{
    private readonly IOptions<MyAppOptions> _configuration;

    public DbSet<Tenant> Tenants { get; set; }

    public PlatformDbContext(DbContextOptions<PlatformDbContext> options, IOptions<MyAppOptions> configuration) : base(options)
    {
        _configuration = configuration;
    }

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        modelBuilder.Entity<Tenant>().ToTable("Tenants");
        modelBuilder.Entity<Tenant>().HasIndex(x => x.Name).IsUnique(true);
    }

    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
    {
        var localConnection = _configuration.Value.PlatformConnectionString;

        optionsBuilder.UseSqlServer(localConnection);
    }
}

public class TenantDbContext : DbContext
{
    public DbSet<Listing> Listings { get; set; }

    public TenantDbContext(DbContextOptions<TenantDbContext> options) : base(options) { }

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        modelBuilder.ApplyConfigurationsFromAssembly(typeof(TenantDbContext).Assembly);
    }
}

Create the models in a new folder [project]/Contracts/Data:

    public class EntityBase
    {
        [Key]
        public int Id { get; set; }
        [Required]
        public DateTime DateCreated { get; set; } = DateTime.UtcNow;
        public DateTime? DateUpdated { get; set; } = null;
    }

    public class Tenant : EntityBase
    {
        [Required, StringLength(100)]
        public string Name { get; set; } = null!;
        [Required, StringLength(20)]
        public string Code { get; set; } = null!;
        [Required, StringLength(500)]
        public string ConnectionString { get; set; } = null!;
    }

    public class Listing : EntityBase
    {
        [Required, StringLength(100)]
        public string Name { get; set; } = null!;
        [Required, StringLength(150)]
        public string Slug { get; set; } = null!;
        [Required, Column(TypeName = "decimal(18,2)")]
        public decimal Price { get; set; }
    }

…then in the Program.cs:

using MultiTenancyDemo.Data;

var builder = WebApplication.CreateBuilder(args);

// Add services to the container.
builder.Services.AddDbContext<PlatformDbContext>();
builder.Services.AddDbContext<TenantDbContext>();

Remember to create the “IEntityTypeConfiguration” implementations to configure the database tables.

Configuring IOptions

To dynamically retrieve the platform’s connection string, configure IOptions.

First, we create the options class first. You can extend it as required by your application to match the appsettings.json file:

{
  "Logging": {
    "LogLevel": {
      "Default": "Information",
      "Microsoft.AspNetCore": "Warning"
    }
  },
  "AllowedHosts": "*",
  "MyAppSettings": {
    "PlatformConnectionString": "Server =.\\SQLExpress;Database=Platform;Trusted_Connection=True;TrustServerCertificate=True;",
  }
}

…then the options class:

public class MyAppOptions
{
    public string PlatformConnectionString { get; set; } = null!;
}

To better organise our code and de-clutter the Program.cs file, create “ConfigurationServiceCollectionExtensions” in a new folder [project]/Extensions:

public static class ConfigurationServiceCollectionExtensions
{
    public static IServiceCollection AddMyOptions(this IServiceCollection services)
    {
        services.AddOptions<MyAppOptions>().BindConfiguration("MyAppSettings");

        return services;
    }
}

…then in the Program.cs:

using MultiTenancyDemo.Data;
using MultiTenancyDemo.Extensions;

var builder = WebApplication.CreateBuilder(args);

// Add services to the container.
builder.Services.AddMyOptions();
builder.Services.AddDbContext<PlatformDbContext>();
builder.Services.AddDbContext<TenantDbContext>();

This should be fine for us to run migrations for the platform database.

Running migrations

We will not be able to run migrations for the TenantDatabase just yet. Remember we will need to know the connection string, which is (securely) stored in the Tenants table. So, let’s create the database on the server, and run the migration.

Add-Migration InitialMigration -Context PlatformDbContext

We should get the following migration:

public partial class InitialMigration : Migration
{
    /// <inheritdoc />
    protected override void Up(MigrationBuilder migrationBuilder)
    {
        migrationBuilder.CreateTable(
            name: "Tenants",
            columns: table => new
            {
                Id = table.Column<int>(type: "int", nullable: false)
                    .Annotation("SqlServer:Identity", "1, 1"),
                Name = table.Column<string>(type: "nvarchar(100)", maxLength: 100, nullable: false),
                Code = table.Column<string>(type: "nvarchar(20)", maxLength: 20, nullable: false),
                ConnectionString = table.Column<string>(type: "nvarchar(500)", maxLength: 500, nullable: false),
                DateCreated = table.Column<DateTime>(type: "datetime2", nullable: false),
                DateUpdated = table.Column<DateTime>(type: "datetime2", nullable: true)
            },
            constraints: table =>
            {
                table.PrimaryKey("PK_Tenants", x => x.Id);
            });

        migrationBuilder.CreateIndex(
            name: "IX_Tenants_Name",
            table: "Tenants",
            column: "Name",
            unique: true);
    }

    /// <inheritdoc />
    protected override void Down(MigrationBuilder migrationBuilder)
    {
        migrationBuilder.DropTable(
            name: "Tenants");
    }
}

..then we apply the migration:

Update-Database -Context PlatformDbContext

The database is created on the server

The next thing we need to do is to add a tenant to the table. We should do that using the platform’s backend which should also create the sub-domain, but that is outside the scope of this blog post. Therefore, lets add it manually into the database.

Note: we are storing the connection string here for simplicity. In production, you will need to ensure that such sensitive data is properly secured, e.g. by encrypting it.

Tenant record added to the database

In the next article, we will use this record to connect to the Tenant database in production. But before we do that, we will also discuss how we will handle running migrations in the same database.