What goes into DbContextOptions when invoking a new DbContext?

I personally don't understand why you wouldn't want to use DI and just let it be created on your behalf by specifying (ApplicationDbContext db) in your controller's constructor, in fact you are registering it in DI with your code anyway:

services.AddDbContext<ApplicationDbContext>(options =>
    options.UseSqlServer(Configuration.GetConnectionString("DefaultConnection")));

If you absolutely want to call new ApplicationDbContext(...) explicitly, keep in mind that it's a bad idea to call Configuration within your controller, but you need the configuration in order to get the connection details as you must provide DbContextOptions as an argument to the context. I suggest removing the services.AddDbContext altogether because are never intending on resolving it. Built in DI doesn't seem to have a clean way of doing "factory" registration. I use Autofac and Simple Injector which provide really clean ways in a lambda expression to do it along these lines:

containerBuilder.Register(c =>
{
    var optionsBuilder = new DbContextOptionsBuilder<EntityDbContext>()
    .UseSqlServer(Configuration.GetConnectionStringSecureValue("DefaultConnection"));

    return optionsBuilder.Options;
});

You would then simply do this:

public GigsController(DbContextOptionsBuilder<EntityDbContext> dbContextOptions)
{
    _context = new ApplicationDbContext(dbContextOptions);
}

So if you were to integrate Autofac that's one way.

I've just managed to get my head around all this injection stuff and configuration and have a nice clean solution which would solve your issue including reading configuration. The idea is you read the configuration from appsettings.json the assign to a connection string property on a configuration class:

public interface IDatabaseConfig
{
    string ConnectionString { get; set; }
}

public class DatabaseConfig : IDatabaseConfig
{
    public DatabaseConfig()
    {
        IConfiguration configuration = new ConfigurationBuilder()
            .AddJsonFile("appsettings.json", optional: true, reloadOnChange: true)
            .Build();

        ConnectionString = configuration.GetSection("Database").GetValue<string>("ConnectionString");
    }

    public string ConnectionString { get; set; }

}

then you register the interface in ConfigureServices:

services.AddTransient<IDatabaseConfig, DatabaseConfig>();

and use the interface as a controller constructor argument then you can create the options:

public GigsController(IDatabaseConfig dbConfig)
{
    var dbContextOptions = new DbContextOptions<ApplicationDbContext>().UseSqlServer(dbConfig.ConnectionString);
    _context = new ApplicationDbContext(dbContextOptions);
}

I'm not a fan of creating the configuration builder directly on that class. ASP.NET Core provides one already and all this should be done in the Startup class. Best practice would be to deserialise the DatabaseConfig from the appsettings with:

var databaseConfig = configuration.GetSection("Database").Get<DatabaseConfig>();

but as I can't see a way to then register that instance or defer that to a DI factory style registration it's not an option.

Really you're simply better off using serices.AddDbContext<ApplicationDbContext>(...) like you originally had and inject it as a constructor argument on your controller and the problem is soved.

The way I personally tackle this whole scenario giving freedom to configure the options as you like as well as even switch out the SqlServer connection for an in memory db for running integration tests where you don't have access to a real db as part on a CI build, is as follows...

I have a DatabaseConfig object graph:

public class Config
{
    public DatabaseConfig Database { get; set; }

}

public interface IDatabaseConfig
{
    InMemoryConfig InMemory { get; set; }
    string ConnectionString { get; set; }
}

public class DatabaseConfig : IDatabaseConfig
{
    public InMemoryConfig InMemory { get; set; }
    public string ConnectionString { get; set; }

}

public class InMemoryConfig
{
    public bool Enabled { get; set; }
    public string Name { get; set; }

}

which is aligned with this structure and deserialized from appsettings.json:

"Database": {
    "InMemory": {
      "Enabled": true,
      "Name": "Snoogans"
    },
    "ConnectionString": "Server=(localdb)\\MSSQLLocalDB;Database=SomeDb;Trusted_Connection=True;"
  }

An out of the box option is to do this

var databaseConfig = configuration.GetSection("Database").Get<DatabaseConfig>();

but I use Autofac and there's a sweet nuget package called Divergic.Configuration.Autofac which allows you to do this in the ConfigureContainer method:

builder.RegisterModule<ConfigurationModule<JsonResolver<Config>>>();

If a property on the Config graph implements an interface then a registration is made with Autofac with the settings deserialized onto the service instance. That in itself is enough to inject IDatabaseConfig as a constructor argument on any controller and you can then new it up yourself, but it's really best done in one place otherwise you have to keep repeating the DbContextOptionsBuilder logic everywhere yo use it.

So I follow the ConfigurationModule registration with a factory registration which creates my db context with the options from the appsettings.json:

containerBuilder.Register(c =>
{
    var optionsBuilder = new DbContextOptionsBuilder<EntityDbContext>();
    optionsBuilder = databaseConfig.InMemory.Enabled
        ? optionsBuilder.UseInMemoryDatabase(databaseConfig.InMemory.Name)
        : optionsBuilder.UseSqlServer(databaseConfig.ConnectionString);

        return optionsBuilder.Options;
});

This is a clean solution and responsibilities don't leak into areas they shouldn't. Your controller shouldn't be responsible for database ORM creation. It should just be given one pre-created to use otherwise it's too hard to change later. Consider if you have 500 controllers where you manually create them in all cases vs passing in a pre-created instance where the creation code is done in one place. Take it one step further where my db context implements IWriteEntities and IReadEntities then it's even more abstract and you could switch in another whole DbContext subclass and you simplify the re-work to a single line where you register the db context.

If you really want to create the context manually, then you can configure it like this:

var optionsBuilder = new DbContextOptionsBuilder<ApplicationDbContext>();
optionsBuilder.UseSqlServer(Configuration.GetConnectionStringSecureValue("DefaultConnection"));
_context = new ApplicationDbContext(optionsBuilder.Options); 

(The DbContextOptionsBuilder<ApplicationDbContext> class is the type of options argument in services.AddDbContext<ApplicationDbContext>(options =>). But in the controller, you don't have access to Configuration object, so you would have to expose it as a static field in Startup.cs or use some other trick, which is all bad practice.

The best way to obtain ApplicationDbContext is to get it through DI:

public GigsController(ApplicationDbContext context)
{
    _context = context;
}

The DI container will take care of instantiating and disposing of ApplicationDbContext. Note that you have everything correctly configured in Startup.cs:

services.AddDbContext<ApplicationDbContext>(options =>
        options.UseSqlServer(Configuration.GetConnectionString("DefaultConnection")));

services.AddIdentity<ApplicationUser, IdentityRole>()
        .AddEntityFrameworkStores<ApplicationDbContext>()
        .AddDefaultTokenProviders();

That's configuring DI, so why not just use it?

One more note about the default constructor of DbContext: In EF6 it was done like this: public ApplicationDbContext(): base("DefaultConnection") {}. Then the base object would use System.Configuration.ConfigurationManager static class to obtain the connection string named DefaultConnection from web.config. The new Asp.net Core and EF Core is designed to be as much decoupled as possible, so it should not take dependencies on any configuration system. Instead, you just pass a DbContextOptions object - creating that object and configuring it is a separate concern.

This is how I would do it:

public class GigsController : Controller
{
    private readonly IConfiguration _configuration;
    private string _connectionString;
    DbContextOptionsBuilder<ApplicationDbContext> _optionsBuilder;

    public GigsController (IConfiguration configuration)
    {
        _configuration = configuration;
        _optionsBuilder = new DbContextOptionsBuilder<ApplicationDbContext>();
        _connectionString = _configuration.GetConnectionString("DefaultConnection");
        _optionsBuilder.UseSqlServer(_connectionString);
    }

    public IActionResult Index()
    {
        using(ApplicationDbContext _context = new ApplicationDbContext(_optionsBuilder.Options))
        {
             // .....Do something here
        }
    }
}

Recently I was migrating a very large dataset into database(around 10 million) and one context instance will quick eat up all my memory. Thus I had to create a new Context instance and dispose the old one after certain threshold to release memory.

This is not an elegant solution but worked for me.