EF Core to call database based on parameter in API

Question

I have an API developed in .NET Core with EF Core. I have to serve multiple clients with different data(but the same schema). This is a school application, where every school want to keep their data separately due to competition etc. So we have a database for each school. Now my challenge is, based on some parameters, I want to change the connection string of my dbContext object.

for e.g., if I call api/students/1 it should get all the students from school 1 and so on. I am not sure whether there is a better method to do it in the configure services itself. But I should be able to pass SchoolId from my client application

public void ConfigureServices(IServiceCollection services)
{
    services.AddDbContext<SchoolDataContext>(options =>
        options.UseSqlServer(Configuration.GetConnectionString("APIConnectionString")));
    services.AddScoped<IUnitOfWorkLearn, UnitOfWorkLearn>();
}

11 May 2021

namespace LearnNew
{
    public class Startup
    {
        public Startup(IConfiguration configuration)
        {
            Configuration = configuration;
        }

        public IConfiguration Configuration { get; }

        // This method gets called by the runtime. Use this method to add services to the container.
        public void ConfigureServices(IServiceCollection services)
        {
            //Comenting to implement Mr Brownes Solution
            //services.AddDbContext<SchoolDataContext>(options =>
            //   options.UseSqlServer(
            //       Configuration.GetConnectionString("APIConnectionString")));



            services.AddScoped<IUnitOfWorkLearn, UnitOfWorkLearn>();


            services.AddControllers();


            services.AddHttpContextAccessor();

            services.AddDbContext<SchoolDataContext>((sp, options) =>
            {
                var requestContext = sp.GetRequiredService<HttpContext>();
                var constr = GetConnectionStringFromRequestContext(requestContext);
                options.UseSqlServer(constr, o => o.UseRelationalNulls());

            });

            ConfigureSharedKernelServices(services);

            services.AddSwaggerGen(c =>
            {
                c.SwaggerDoc("v1", new OpenApiInfo { Title = "LearnNew", Version = "v1" });
            });
        }

        private string GetConnectionStringFromRequestContext(HttpContext requestContext)
        {
            //Trying to implement Mr Brownes Solution
            var host = requestContext.Request.Host;
            // Since I don't know how to get the connection string, I want to  
            //debug the host variable and see the possible way to get details of 
            //the host. Below line is temporary until the right method is identified
            return Configuration.GetConnectionString("APIConnectionString");
        }

        private void ConfigureSharedKernelServices(IServiceCollection services)
        {
            ServiceProvider serviceProvider = services.BuildServiceProvider();
            SchoolDataContext appDbContext = serviceProvider.GetService<SchoolDataContext>();

            services.RegisterSharedKernel(appDbContext);
        }

            // This method gets called by the runtime. Use this method to configure the HTTP request pipeline.
            public void Configure(IApplicationBuilder app, IWebHostEnvironment env)
        {
            if (env.IsDevelopment())
            {
                app.UseDeveloperExceptionPage();
                app.UseSwagger();
                app.UseSwaggerUI(c => c.SwaggerEndpoint("/swagger/v1/swagger.json", "LearnNew v1"));
            }

            app.UseHttpsRedirection();

            app.UseRouting();

            app.UseAuthorization();

            app.UseEndpoints(endpoints =>
            {
                endpoints.MapControllers();
            });
        }



    }
}

Answer 1

You can access the HttpContext when configuring the DbContext like this:

        services.AddControllers();

        services.AddHttpContextAccessor();

        services.AddDbContext<SchoolDataContext>((sp, options) =>
        {
            var requestContext = sp.GetRequiredService<IHttpContextAccessor>().HttpContext;
            var constr = GetConnectionStringFromRequestContext(requestContext);
            options.UseSqlServer(constr, o => o.UseRelationalNulls());

        });

This code:

            var requestContext = sp.GetRequiredService<IHttpContextAccessor>().HttpContext;                var constr = GetConnectionStringFromRequestContext(requestContext);
            options.UseSqlServer(constr, o => o.UseRelationalNulls());

will run for every request, configuring the connection string based on details from the HttpRequestContext.

If you need to use your DbContext on startup, don't resolve it through DI. Just configure a connection like this:

        var ob  = new DbContextOptionsBuilder<SchoolDataContext>();
        var constr = "...";
        ob.UseSqlServer(constr);
        using (var db = new Db(ob.Options))
        {
            db.Database.EnsureCreated();
        }

But in production you would normally create all your tenant databases ahead-of-time.