How do I get the connection string from the SqlServerDBContextOptionsExtensions in ASP.Net Core

Question

I am building up an ASP.Net Core API and I have not been able to find a way to get the connection string from the DBContextOptions.

I have the DBContext in my startup.cs as shown below;

public void ConfigureServices(IServiceCollection services)
{
    // Add framework services.
    services.AddApplicationInsightsTelemetry(Configuration);

    services.AddEntityFrameworkSqlServer()
        .AddDbContext<MainContext>(options => options.UseSqlServer(Configuration.GetConnectionString("MainConnection")));

    services.AddMvc()
       .AddJsonOptions(opt =>
        {
            opt.SerializerSettings.ContractResolver = new CamelCasePropertyNamesContractResolver();
        });
}

And in my Context class, I have the following constructor;

public MainContext(DbContextOptions<MainContext> options) : base(options)
{

}

but it doesn't work unless I add an actual connection string in the DBContext class, OnConfiguring method, as shown below;

protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
{
    //TODO Move connection string to a secure location
    optionsBuilder.UseSqlServer(@"Server= .....");

}

I can see that the Startup.cs is getting the correct connection string from the appsettings.json file when I debug and examine the value in Configuration.GetConnectionString("MainConnection").

I would think that passing the options into the DbContext class via DI would have passed the connection string but the DbContect class doesn't work unless I have that optionBuilder.UseSqlServer() in the OnConfiguring method.

I found this SO post https://mcmap.net/q/750717/-asp-net-5-multiple-dbcontext-problems, that talks about using the following code to extract the connection string from the options property

public ResourceDbContext(DbContextOptions options) : base(options)
{
    _connectionString = ((SqlServerOptionsExtension)options.Extensions.First()).ConnectionString;
}


protected override void OnConfiguring(DbContextOptionsBuilder options)
{
    options.UseSqlServer(_connectionString);
}  

But when I try to use it, I find that there is no longer a First() method in options.Extensions

So, my first question is...

Why doesn't the DBContext class work without having to add the connection string in the OnConfiguring method

My second question is ...

If the connection string is required in the OnCOnfiguring method, how can I get it from the DbContextOptions options object rather than having to explicitly provide it in the OnConfiguring method --> optionsBuilder.UseSqlServer(@"Server= .....");

Elfont

Answer 1

Inside of your appsettings.json you would create the following:

{
     "Database" : {
          "ConnectionString" : "..."
      }
}

Then inside your ConfigureServices you would do the following:

services.AddSingleton(_ => Configuration);

That will basically populate the IConfigurationRoot property. Which you can inject anywhere, and have access to the connection string by doing:

private readonly IConfigurationRoot configuration;
private IDbConnection dbConnection { get; }

public Example(IConfigurationRoot configuration)
{
     this.Configuration = configuration;
     dbConnection = new SqlConnection(this.configuration.GetConnectionString("..."));
}

I structured this a bit odd, you really would simply pass the ConnectionString to another class, or method to inject but this demonstrates for you. But I believe Entity Framework 7 has a factory to directly accept the connection string. Hopefully this helps you out.

In Entity Framework it should be like this inside your ConfigureServices:

services.AddSingleton<dbContext>(_ => new dbContext(Configuration.GetConnectionString("...")));
public class dbContext : DbContext
{
     public dbContext(string dbConnection) : base(dbConnection)
     {

     }
}

Some extra documentation.

Hellgrammite

Answer 2

At least for EF Core 1.1, you need to use FindExtension<SqlServerOptionsExtension>()

using Microsoft.EntityFrameworkCore;
using Microsoft.EntityFrameworkCore.Infrastructure.Internal;

namespace MyNamespace
{
    public class MyContext : DbContext
    {
        public MyContext(DbContextOptions<MyContext> options) : base(options)
        {
            var sqlServerOptionsExtension = 
                   options.FindExtension<SqlServerOptionsExtension>();
            if(sqlServerOptionsExtension != null)
            {
                string connectionString = sqlServerOptionsExtension.ConnectionString;
            }
        }
    }
}

The null check is there in case you're using opt.UseInMemoryDatabase() in your Startup.cs

Critical

Answer 3

For EntityFrameworkCore 2.1.4,your connection strings should be like this in appsettings.json

  "ConnectionStrings": {
    "Connection1": "....",
    "Connection2": "..."
  },

Add following line to the ConfigureServices method in Startup.cs

 services.AddSingleton(provider => Configuration);

After these lines,

 services.AddDbContext<MyContextContext>
            (options => options.UseSqlServer(Configuration.GetConnectionString("Connection1")));
 services.AddScoped<DbContext, MyContext>();

And your database context class should be modified as follows.

public partial class MyContext : DbContext
    {
        private readonly IConfiguration _configuration;
        private IDbConnection DbConnection { get; }

        public MyContext(DbContextOptions<MyContext> options, IConfiguration configuration)
            : base(options)
        {
            this._configuration = configuration;
            DbConnection = new SqlConnection(this._configuration.GetConnectionString("Connection1"));
        }

        protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
        {
            if (!optionsBuilder.IsConfigured)
            {
                optionsBuilder.UseSqlServer(DbConnection.ToString());
            }
        }
    }

Pliable

What if context class has constructor like this also public pixelleContext() { } and code which using context is as follow using (var context = new pixelleContext()) { return context.Specsheet.ToList(); } – Montemontefiascone