dotnetef用于開發/生產的獨立資料庫提供程式-有解無憂

嘗試在本地開發中使用 sqlite，在生產中使用 sqlserver。
如果我根據環境切換提供程式，仍然存在僅為其中一個提供程式生成遷移的問題（sqlite 遷移不適用于 sqlserver，反之亦然）。

為開發使用單獨的資料庫而不是在生產中使用單獨的資料庫的可行方法是什么？

uj5u.com熱心網友回復：

訣竅是為 dev 創建一個單獨的 dbcontext，讓它繼承主 dbcontext 并只更改連接字串。從那里開始，為主要 dev dbcontexts 生成遷移，然后根據環境選擇要使用的背景關系。

代碼設定

主 dbcontext 將使用依賴注入來獲取包含連接字串的配置。

using app.Models;
using Microsoft.EntityFrameworkCore;
using Microsoft.Extensions.Configuration;

namespace app.Data
{
    public class AppDbContext : DbContext
    {
        protected readonly IConfiguration Configuration;

        public AppDbContext(IConfiguration config)
        {
            Configuration = config;
        }

        protected override void OnConfiguring(DbContextOptionsBuilder options)
        {
            options.UseSqlServer(Configuration["SQLConnectionString"]);
        }

        // whatever your app does
        public DbSet<Dog> Dogs { get; set; }
    }
}

dev dbcontext 將只繼承主要的，使用不同的配置塊來指定 sqlite。

using app.Models;
using Microsoft.EntityFrameworkCore;
using Microsoft.Extensions.Configuration;

namespace app.Data
{
    public class DevAppDbContext : AppDbContext
    {
        public DevAppDbContext(IConfiguration config) : base(config)
        {
        }

        protected override void OnConfiguring(DbContextOptionsBuilder options)
        {
            options.UseSqlite(Configuration.GetConnectionString("app"));
        }
    }
}

為了我們appsettings.Development.json，我們將增加dev的資料庫連接字串

...
  "ConnectionStrings": {
    "app": "Data Source=app.db"
  }
}

In startup.cs we will add the IWebHostEnvironment via dependency injection to get the environment. If in dev, then we will specify the additional generic parameter specifying the dev dbcontext as the implementation.

from the docs in vscode, the method signature is: public static IServiceCollection AddDbContext<TContextService, TContextImplementation>


        public Startup(IConfiguration configuration, IWebHostEnvironment env)
        {
            _config = configuration;
            _env = env;
        }

        private readonly IConfiguration _config;
        private readonly IWebHostEnvironment _env;

        public void ConfigureServices(IServiceCollection services)
        {
            if (_env.IsDevelopment())
                services.AddDbContext<AppDbContext, DevAppDbContext>();
            else
                services.AddDbContext<AppDbContext>();
            services.AddDatabaseDeveloperPageExceptionFilter();

Adding migrations

Adding migrations now requires switching environments and specifying the dbcontext we are creating migrations for. This can be abstracted to a fairly simple powershell script:

[CmdletBinding()]
param(
    [string]$Name
);

$prev = $Env:ASPNETCORE_ENVIRONMENT;
Write-Host "Previous env=""$prev""";
try {
    Write-Host "Adding migration for production";
    $Env:ASPNETCORE_ENVIRONMENT = "Production";
    dotnet ef migrations add $Name `
        --context "AppDbContext" `
        --output-dir "Migrations/SqlServerMigrations";
        
    Write-Host "Adding migration for development";
    $Env:ASPNETCORE_ENVIRONMENT = "Development";
    dotnet ef migrations add $Name `
        --context "DevAppDbContext" `
        --output-dir "Migrations/SqliteMigrations";
}
finally {
    Write-Host "Restoring env=""$prev""";
    $Env:ASPNETCORE_ENVIRONMENT = $prev;
}

Example usage:

> .\scripts\add-migration.ps1 -Name init
Previous env=""
Adding migration for production
Build started...
Build succeeded.
Done. To undo this action, use 'ef migrations remove'
Adding migration for development
Build started...
Build succeeded.
Done. To undo this action, use 'ef migrations remove'
Restoring env=""

Applying migrations

Same as creating migrations, we need to switch contexts depending on the environment. This time though, we will only update a single environment as specified by the user when invoking the script.

[CmdletBinding()]
param(
    [string]
    [ValidateSet("Development", "Production")]
    $Environment
)

$context = @{
    "Development" = "DevAppDbContext" 
    "Production"  = "AppDbContext"
}.$Environment;

$prev=$Env:ASPNETCORE_ENVIRONMENT;
try {
    $Env:ASPNETCORE_ENVIRONMENT=$Environment;
    dotnet ef database update --context $context;
}
finally {
    Write-Host "Restoring env=""$prev""";
    $Env:ASPNETCORE_ENVIRONMENT = $prev;
}

Usage example:

> .\scripts\apply-migrations.ps1 -Environment Development
Setting environment to Development
Build started...
Build succeeded.
No migrations were applied. The database is already up to date.
Done.
Restoring env=""

Conclusion

This should be enough to get started with using sqlite for local development, and sqlserver for live. Some limitations of this approach is that sqlite doesn't support all the features of sqlserver, so you will either have to set up a dev sqlserver db as well, or will need to decide to avoid using those features.

dotnetef用于開發/生產的獨立資料庫提供程式

代碼設定

Adding migrations

Applying migrations

Conclusion

Further reading