Change connection string based on country parameter coming from the controller

Question

I have a few databases with the same schema but every country has its own database.

Company_DE Company_FR ...

The endpoints always need the country code :

/{countryCode}/get-companies

How can I set the DbContext based on the countryCode in the url? If the country code in the endpoint is "DE" for example?

I'm using .NET Core 5.0 with Entity Framework Core.

Answer 1

You can store sql information in combination with the Countrycodes in a main table or some other way you like. Then create a dbcontext similar to this:

public class ClientDatabaseContext : DbContext
    {
        private readonly ClientDatabase clientDatabase;
        public ClientDatabaseContext(ClientDatabase clientDatabase) { this.clientDatabase = clientDatabase; }

        /// <summary>
        /// Connects dynamicly to given client database
        /// </summary>
        protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
        {
            optionsBuilder.UseSqlServer($"Server=sql-01-tst;Database={clientDatabase.DatabaseName};User Id={clientDatabase.Username};Password={clientDatabase.Password};Trusted_Connection=True;MultipleActiveResultSets=true");
        }

        public virtual DbSet<ActivityLog> ActivityLogs { get; set;}


        protected override void OnModelCreating(ModelBuilder modelBuilder)
        {
            base.OnModelCreating(modelBuilder);

            modelBuilder.Entity<ActivityLog>().HasKey(al => al.Id);
        }
    }

Then just can just pass your own version of "ClientDatabase" when creating the dbcontext.

This has the downside that it does not work with dependency injection and you need to create a dbcontext in code. But its allows you to use dynamic DbContexts