asp.net-coreentity-framework-coremulti-database

Handling multi-databases structure


I'm trying to implement multi-database structure with automatic migrations. databases uncountable and i can't set the a fixed connection strings.

I tried many ways to handle it, some ways worked but could not handle automatic migrations. I have two different DbContexts and different connection strings.

Is this a good way to handle it or there is a better one?

public class CategoriesController : Controller
{
    private readonly UserDbContext _context;

    public CategoriesController(UserDbContext context, ApplicationDbContext _Maincontext)
    {
        var conn = _Maincontext.Users.FirstOrDefault().DbId;
        context.Database.SetConnectionString($"Data Source=.\\SQLEXPRESS;Initial Catalog={conn};Integrated Security=False; uid=sa;password=123;");
        context.Database.Migrate();
        _context = context;
    }

    // GET: Categories
    public async Task<IActionResult> Index()
    {
        return Ok(await _context.Categories.ToListAsync());
    }
}

Solution

  • I've used the Users Claims to handle this problem


    Startup

            public void ConfigureServices(IServiceCollection services)
            {
                // First Context which has a static connection string
                services.AddDbContext<ApplicationDbContext>(options =>
                    options.UseSqlServer(
                        Configuration.GetConnectionString("mainDb")));
    
                // To inject HttpContext for each request
                services.TryAddSingleton<IHttpContextAccessor, HttpContextAccessor>();
    
                // Second Context which has a dynamic connection strings
                services.AddDbContext<UserDbContext>();
    
                ).AddEntityFrameworkStores<ApplicationDbContext>();
                services.AddControllersWithViews();
            }
    

    UserDbContext class

     public class UserDbContext : DbContext
        {
            private readonly HttpContext _httpContext;
    
            public UserDbContext(DbContextOptions<UserDbContext> options, IHttpContextAccessor httpContextAccessor = null)
                : base(options)
            {
                _httpContext = httpContextAccessor?.HttpContext;
            }
    
            //..
    
            protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
            {
                if (!optionsBuilder.IsConfigured)
                {
                    //First get user claims    
                    var claims = _httpContext?.User.Claims.ToList();
    
                    //Filter specific claim    
                    string dbName = claims?.FirstOrDefault(x => x.Type.Equals("db", StringComparison.OrdinalIgnoreCase))?.Value;
    
    
                    if (dbName == null) dbName = "TempDebugDb";
                    optionsBuilder.UseSqlServer(GetConnectionString(dbName));
                 
                }
            }
    
            private static string GetConnectionString(string dbName)
            {
                return $"Data Source=.\\SQLEXPRESS;Initial Catalog={dbName};Integrated Security=False; uid=sa;password=*****;";
            }
    
    
            public DbSet<Category> Categories { get; set; }
    
    
            protected override void OnModelCreating(ModelBuilder modelBuilder)
            {
                base.OnModelCreating(modelBuilder);
            }
            
        }