How to inject DbContext with dynamic SQL connection based on a value received from other system in a controller

Question

Using C#, ASP.NET 8.0 (MVC), EF Core 8.0 (new to EF Core).

I have a situation where I have couple of database connections in AppSettings.Json.

Now I need to pass respective database connection string based on a specific value received from a 3rd party system with in a controller (e.g. HomeController).

So what is the best way to pass DbContext with appropriate connection string from a Controller using Dependency Injection.

Any sample code you can provide would help me.

Thank you in advance.

Created a database context static class, using that I am creating a DBContext within Controller. Simplified code below

public static class DbContextFactory
{
    public static Dictionary<string, string>? DbConnectionStrings { get; set; }

    public static void SetDbConnectionString(Dictionary<string, string>? dbConnectionStrings)
    {
        DbConnectionStrings = dbConnectionStrings;
    }

    public static ApplicationContext Create(string dbConnId)
    {
        DbContextOptionsBuilder<ApplicationContext> optionsBuilder = new();
        optionsBuilder.UseSqlServer(DbConnectionStrings![dbConnId]);
        return new ApplicationContext(optionsBuilder.Options);
    }
}

My Controller

public IActionResult GetGroupMemebers(string userid)
{
    try
    {
        string databaseId = GetUserDatabaseFromCustomApplication(userid);
        appContext ??= DbContextFactory.Create(databaseId.ToLower());        

       IEnumerable<TeamOwner>? teamOwners = databaseService.GetTeamOwnersAsync(appContext, userid);
        
        //other application logic
        
    }
    catch (Exception ex)
    {       
        return View("Index", grpMemVM);
    }
}

Answer 1

Based on your description and the code you provided, do you want to create the corresponding DbContext instance after receiving the database connection string through the controller? If so, here is my example method you can use as a reference:

In my AppSettings.json I have multiple database connection strings:

  "ConnectionStrings": {
    "DropdownContext": "Server=(localdb)\\mssqllocaldb;Database=DropdownContext;Trusted_Connection=True;MultipleActiveResultSets=true",
    "WebApplication7ContextConnection": "Server=(localdb)\\mssqllocaldb;Database=CCOO;Trusted_Connection=True;MultipleActiveResultSets=true"
  },

I create a custom IDbContextFactory interface and its implementation class:

 public interface IDbContextFactory
 {
     MyDbContext CreateDbContext(string connectionString);
 }

     public class DbContextFactory : IDbContextFactory
     {
         public MyDbContext CreateDbContext(string connectionString)
         {
             var optionsBuilder = new DbContextOptionsBuilder<MyDbContext>();
             optionsBuilder.UseSqlServer(connectionString);
    
             return new MyDbContext(optionsBuilder.Options);
         }
     }

About Mydbcontext:

public class MyDbContext : DbContext
{
    public MyDbContext(DbContextOptions<MyDbContext> options)
        : base(options)
    {
    }

    
    public DbSet<Class> Myclass { get; set; }
}

My controller:

 public IActionResult Ondex(string dbKey)
  {
      string connectionString = _configuration.GetConnectionString(dbKey);
      var context = _dbContextFactory.CreateDbContext(connectionString);

      var data = context.Myclass.ToList();
      return View(data);
  }

When I send the correct database connection string, It can be observed that relevant information is obtained: