Using multiple connection strings

Question

Info
I have multiple projects in my Solution, of which one is the DAL and the other is an ASP.NET MVC6 project. Since the MVC6 project is also the startup project I need to add my connection string there.

I saw this solution, but it is not accepted, nor does it work.

My Try
appsettings.json

"Data": {
  "DefaultConnection": {
    "ConnectionString": "Server=.\\SQLEXPRESS;Database=Bar;Trusted_Connection=True;MultipleActiveResultSets=true"
  },
  "FooBar": {
    "ConnectionString": "Server=.\\SQLEXPRESS;Database=Bar;Trusted_Connection=True;MultipleActiveResultSets=true"
  }
}

Startup.cs

public void ConfigureServices(IServiceCollection services)
{
    // Add framework services.
    services.AddEntityFramework()
        .AddSqlServer()
        .AddDbContext<ApplicationDbContext>(options =>
            options.UseSqlServer(Configuration["Data:DefaultConnection:ConnectionString"]))
             .AddDbContext<ApplicationDbContext>(options =>
            options.UseSqlServer(Configuration["Data:FooBar:ConnectionString"]));
}

Yet, when I try to access data using the FooBar connection string I get the following message:

"Additional information: No connection string named 'FooBar' could be found in the application config file."

The Question
How do I get multiple connection strings working?

Answer 1

If you take a look at the official documentation for connection strings in asp.net core their example shows the connection string stored in appsettings.json like this

{
  "ConnectionStrings": {
    "BloggingDatabase": "Server=(localdb)\\mssqllocaldb;Database=EFGetStarted.ConsoleApp.NewDb;Trusted_Connection=True;"
  },
}

Which, when adapted to your example would become.

{
  "ConnectionStrings": {
    "DefaultConnection": "Server=.\\SQLEXPRESS;Database=Bar;Trusted_Connection=True;MultipleActiveResultSets=true",
    "FooBar": "Server=.\\SQLEXPRESS;Database=Bar;Trusted_Connection=True;MultipleActiveResultSets=true"
  }
}

Configuring the context in Startup.cs with the configuration string being read from configuration would use the GetConnectionString() method with the configuration key

public void ConfigureServices(IServiceCollection services) {
    // Add framework services.
    services
        .AddEntityFramework()
        .AddSqlServer()
        .AddDbContext<ApplicationDbContext>(options =>
            options.UseSqlServer(Configuration.GetConnextionString("DefaultConnection")))
        .AddDbContext<ApplicationDbContext>(options =>
            options.UseSqlServer(Configuration.GetConnextionString("FooBar")));
}

Now one observed issue with how the above context is configured in the original question is that there are now two connection strings for the same context.

Trying to use multiple connection strings to work for the same context will cause problems as the framework would not know which option to use when requesting the context.