Entity Framework Core migration - connection string

Question

I'm having a problem to handle the DB connection string in conjunction with migrations. I have 2 projects:

• Domain
• Application

The DbContext is in the Domain project, so this is the project I run migrations against. The migrations concept enforces me to implement OnConfiguring in my DbContext and therein specify the database provider, eg:

protected override void OnConfiguring(DbContextOptionsBuilder builder)
{
    builder.UseSqlServer("<connection string>");
}

My problem is that I don't want to use a hard coded connection string, for obvious reasons, and I cannot use ConfigurationManager to read it from the config file since the config file is in the application project.

Answer 1

Assuming your DbContext class has a constructor that accepts a parameter of type DbContextOptions, the dotnet ef commands have native support for this scenario - requiring no code changes nor additional configuration. Just use the "--startup-project" and "--project" parameters when creating and running migrations.

For example, let's say you have a "Application" project with your configuration and a separate project called "Domain" where the DbContext is implemented.

Context:

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

Startup:

services.AddDbContext<MyContext>(options => 
            options.UseSqlServer(Configuration.GetConnectionString("connection"))

CLI command:

dotnet ef database update --startup-project Application --project Domain

Answer 2

I was using OnConfiguring below with configured in Windows environment variable MsSql.ConnectionString and command for initial ef migration creation started to work: dotnet ef migrations add InitialCreate

protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
{
    var connectionString = Environment.GetEnvironmentVariable("MsSql.ConnectionString");
    if(string.IsNullOrEmpty(connectionString))
        throw new ConfigurationErrorsException("Sql server connection string configuration required");
    
    if (!optionsBuilder.IsConfigured)
    {
        optionsBuilder
            .UseSqlServer(connectionString)
            .UseQueryTrackingBehavior(QueryTrackingBehavior.NoTracking);
    }
}

To configure environment variable:

1. use Win + R hotkeys kombination to open Run command window
2. Type systempropertiesadvanced and hit Enter
3. On Advanced tab click Environment Variables
4. Click New... button
5. In Variable name field type MsSql.ConnectionString
6. In Variable value field type your connection string value

Make sure console(and any program that starts console) is restarted after new variable addition and before running dotnet ef related commands

Answer 3

I have my DBContext in my console app and was using a ctor with few parameters (such as connection string etc), since EF Core Migrations was using the default parameter less ctor and hence the connection string wasn't being populated I had the migrations failing.

Just added code to get the connection string from ConfigurationBuilder within my default ctor to by pass this.

Was only playing around with console app and EF Core so this works for me for now.

Answer 4

Here's how I do it, without a lot of extra code or craziness.

Project Structure:

• AspNetCoreProject.Web

• AspNetCoreProject.Data <-- DbContext here

My DbContext is set up with the constructor that allows you to inject the DbContextOptions

AspNetCoreProject.Data

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

In your application or web application, you set up your ConfigureServices normally.

AspNetCoreProject.Web / Startup.cs / ConfigureServices()

services.AddDbContext<MyContext>(options => 
            options.UseSqlServer(Configuration.GetConnectionString("connection"))

Now, what about migrations? Well, I "trick" the Visual Studio UI into working as expected.

• First, make sure your application (AspNetCoreProject.Web project with Startup.cs) is the start up project.

• Second, open up your Nuget Package Manager Console. At the top of the Nuget PM> Console, there's a dropdown for 'Set Default Project', point this to your AspNetCoreProject.Data or project with the DbContext class.

• Run your migration commands normally. add-migration init then update-database

Answer 5

All the examples I've seen involve either hard-coding the connection string or putting it in my ASP.NET Core application's settings files.

If you aren't using ASP.NET Core, or maybe, I don't know, don't want to have your local environment's database details committed to source control, you can try using a temporary environment variable.

First, implement IDesignTimeDbContextFactory like this (note that IDbContextFactory is now deprecated):

public class AppContextFactory: IDesignTimeDbContextFactory<AppContext>
{
    public AppContextFactory()
    {
        // A parameter-less constructor is required by the EF Core CLI tools.
    }

    public AppContext CreateDbContext(string[] args)
    {
        var connectionString = Environment.GetEnvironmentVariable("EFCORETOOLSDB");
        if (string.IsNullOrEmpty(connectionString))
            throw new InvalidOperationException("The connection string was not set " +
            "in the 'EFCORETOOLSDB' environment variable.");

         var options = new DbContextOptionsBuilder<AppContext>()
            .UseSqlServer(connectionString)
            .Options;
        return new AppContext(options);
    }
}

Then, you can include the environment variable when you call Update-Database, or any of the other EF Core tools:

$env:EFCORETOOLSDB = "Data Source=(local);Initial Catalog=ApplicationDb;Integrated Security=True"; Update-Database

Answer 6

We've had a same issue and there is a solution. :)

You have to implement IDbContextFactory<TContext> When doing so you can read the connectionstrings from your appsettings.json. You can also use Add-Migration without errors, because overwriting OnConfigure() is obsolete then.

Sample implementation:

public class DomainContextFactory : IDbContextFactory<DomainContext>
{
    public string BasePath { get; protected set; }

    public DomainContext Create()
    {
        var environmentName = Environment.GetEnvironmentVariable("ASPNETCORE_ENVIRONMENT");

        var basePath = AppContext.BaseDirectory;

        return Create(basePath, environmentName);
    }

    public DomainContext Create(DbContextFactoryOptions options)
        => Create(options.ContentRootPath, options.EnvironmentName);

    private DomainContext Create(string basePath, string environmentName)
    {
        BasePath = basePath;
        var configuration = Configuration(basePath, environmentName);
        var connectionString = ConnectionString(configuration.Build());
        return Create(connectionString);
    }

    private DomainContext Create(string connectionString)
    {
        if (string.IsNullOrEmpty(connectionString))
        {
            throw new ArgumentException($"{nameof(connectionString)} is null or empty", nameof(connectionString));
        }
        var optionsBuilder = new DbContextOptionsBuilder<DomainContext>();
        return Configure(connectionString, optionsBuilder);
    }

    protected virtual IConfigurationBuilder Configuration(string basePath, string environmentName)
    {
        var builder = new ConfigurationBuilder()
            .SetBasePath(basePath)
            .AddJsonFile("constr.json")
            .AddJsonFile($"constr.{environmentName}.json", true)
            .AddEnvironmentVariables();
        return builder;
    }

    protected virtual string ConnectionString(IConfigurationRoot configuration)
    {
        string connectionString = configuration["ConnectionStrings:DefaultConnection"];
        return connectionString;
    }

    protected virtual DomainContext Configure(string connectionString, DbContextOptionsBuilder<DomainContext> builder)
    {
        builder.UseSqlServer(connectionString, opt => opt.UseRowNumberForPaging());

        DomainContext db = new DomainContext(builder.Options);
        return db;
    }


    DomainContext IDbContextFactory<DomainContext>.Create(DbContextFactoryOptions options)
        => Create(options.ContentRootPath, options.EnvironmentName);
}

How we use it:

    public override IServiceResult<IList<Datei>> LoadAllData()
    {
        using (var db = this.DomainContextFactory.Create())
        {
            var files = db.Datei
                .ToListAsync<Datei>();

            return new ServiceResult<IList<Datei>>(files.Result, files.Result.Count);
        }
    }

sample config

{
  "ConnectionStrings": {
    "DefaultConnection": "Put your connectionstring here"
  }
}