Efcore: Different Database Providers Cannot Be Used in Different Environments

[chrisoverzero]

For ASP.NET Core projects, my team would like to use Entity Framework Core. We would also like the convenience and cross-Windows-Linux-ability of using SQLite for local development, and the power of SQL Server in production.

This doesn't seem to work. When an ASP.NET Core project is configured to use SQLite in "Development" and SQL Server in "Production", the two shall never meet. The option --environment is no help here, as we would still have to overwrite migrations on deployment somehow(?), and the production connection string is not accessible to the build server.

Steps to reproduce

In an ASP.NET Core project, configure EF like this in the class StartupDevelopment:

```c#
services.AddDbContext(opt =>
// This will read from appsettings.Development.json
opt.UseSqlite(Configuration.GetConnectionString("nameOfConnectionString")));

...and like this in the class `Startup`:

```c#
services.AddDbContext<TheContext>(opt =>
    // This will read from appsettings.Production.json for this example, from the environment f'realz.
    opt.UseSqlServer(Configuration.GetConnectionString("nameOfConnectionString")));

Have an entity with a time-based property, such as:

```c#
public class Job
{
public long Id { get; set; }
public DateTimeOffset CreatedAt { get; set; }
}

...and have an `OnModelCreating` overload like this:

```c#
protected override void OnModelCreating(ModelBuilder modelBuilder)
{
    modelBuilder.Entity<Job>()
        .Property(j => j.CreatedAt)
        .ValueGeneratedOnAdd()
        .ForSqliteHasDefaultValueSql("datetime('now')")
        .ForSqlServerHasDefaultValueSql("SYSDATETIMEOFFSET()");
}

Add a migration, probably "InitialCreate", and open the migration code file. You will see:

c# migrationBuilder.CreateTable( name: "Jobs", columns: table => new { Id = table.Column<long>(nullable: false) .Annotation("Sqlite:Autoincrement", true), CreatedAt = table.Column<DateTimeOffset>(nullable: false, defaultValueSql: "datetime('now')") }, constraints: table => { table.PrimaryKey("PK_Jobs", x => x.Id); });

...which is indelibly tied to SQLite.

Further technical details

• EF Core version: 1.1.1
• Database Provider: Microsoft.EntityFrameworkCore.Sqlite, Microsoft.EntityFrameworkCore.SqlServer
• Operating system: Windows 10
• IDE: Visual Studio 2017

[kjbetz]

Would you need to use two different contexts, one for Development TheContextSqlite, and one for Production TheContextMsSql?

[chrisoverzero]

As far as I know, you need access to the connection string to create a migration. Is this incorrect?

Additionally, wouldn't I need to re-architect my entire application to expect the superclass DbContext where the dependency is injected?

[kjbetz]

@chrisoverzero Hmm... I'm not sure. I don't know if you can do it without doing separate contexts... so you would set up different contexts, then load them the way you're doing separately with your different environment Startup classes.

Also, you could then maybe set up a ITheContext interface and then set up the correct service in DI for each environment. services.AddScoped<ITheContext, TheContextSqlite>(); for dev, and services.AddScoped<ITheContext, TheContextMsSql>(); for prod. (I think it would be scoped... but I'm not sure.)

Also, I'm not an expert at ANY of this, but this is how I think it would go if I've learned anything the past year. I'm not sure if this is right or not... I sort of had hopes of doing this as well, but just ended up using PostgreSql for everything dev and production.

[chrisoverzero]

I'm willing to try the 2-contexts idea, but I don't think I can create a migration for the notional TheContextMsSql without access to a connection string. Does that sound right, or am I remembering something else?

[kjbetz]

dotnet ef migration add -c TheContextMsSql -e Production ?

That would then grab your appsettings.Production.json, I think.

[chrisoverzero]

I split the DbContext into two classes, named unimaginatively.

$ dotnet ef migrations add -c DevelopmentJobContext -e Development InitialCreate

Build succeeded.
    0 Warning(s)
    0 Error(s)

Time Elapsed 00:00:03.13
Done. To undo this action, use 'ef migrations remove'

Development works well, as usual.

$ dotnet ef migrations add -c ProductionJobContext -e Production InitialCreate

Build succeeded.
    0 Warning(s)
    0 Error(s)

Time Elapsed 00:00:04.81
System.ArgumentNullException: Value cannot be null.
Parameter name: connectionString
<lots of stack trace follows>

Production requires a connection string. On the off-chance, I tried giving ProductionJobContext a default constructor, in addition to the one it already had. No joy.

[kjbetz]

Dumb question... but you do have a appsettings.Production.json in your folder right? And you do have something like this at your Startup constructor:

```C#
public Startup(IHostingEnvironment env)
{
_env = env;

var builder = new ConfigurationBuilder()
    .SetBasePath(_env.ContentRootPath)
    .AddJsonFile("appsettings.json")
    .AddJsonFile($"appsettings.{_env.EnvironmentName}.json", optional: true)
    .AddEnvironmentVariables();

Configuration = builder.Build();

}
```

The important line in this case being: AddJsonFile($"appsettings.{_env.EnvironmentName}.json", optional: true)

[chrisoverzero]

The production connection string is not accessible anywhere but on production servers, via an environment variable. It is not checked in to the code repository.

[smitpatel]

Try using a fake connection string inside UseSqlServer while generating migration. Fake connection string should work for all the tasks which does not require connection to server (like creating migration file)

Also, the migration files are provider agnostics, (i.e. does not throw error with different provider) but it may not reflect same configuration. As you may have noticed that it creates different code for Identity column in SqlServer & SQLite. Hence you would need to create 2 sets of migration with each provider.

[chrisoverzero]

Thaaaaaat did it. Thank you so much.

I wish I'd thought of that. I was confused that the context needed the connection string, so I assumed that it needed-needed the connection string, not that it was only a prerequisite to construction. It feels weird, but it's better to be weird than to be blocked.