EF Core 2.0迁移完成后自动执行SQL文件的ASP.NET Zero最佳实践问询

Hey there! I’ve worked extensively with ASP.NET Zero (built on the ABP Framework) and have faced this exact scenario before. Here’s a clean, maintainable solution that eliminates the hassle of embedding SQL directly in migrations:

First, build a dedicated class to handle loading and executing your SQL file. This keeps your script logic separated and easy to update:

public class PostMigrationSqlExecutor : ITransientDependency
{
    private readonly YourProjectDbContext _dbContext;
    private readonly IHostingEnvironment _hostingEnv;

    public PostMigrationSqlExecutor(YourProjectDbContext dbContext, IHostingEnvironment hostingEnv)
    {
        _dbContext = dbContext;
        _hostingEnv = hostingEnv;
    }

    public async Task ExecuteScriptAsync()
    {
        // Adjust the path to match your project structure
        var scriptPath = Path.Combine(_hostingEnv.ContentRootPath, "MigrationScripts", "PostMigrations.sql");
        
        if (!File.Exists(scriptPath))
        {
            throw new FileNotFoundException("Post-migration SQL script not found!", scriptPath);
        }

        var sqlContent = await File.ReadAllTextAsync(scriptPath);
        
        // For EF Core 2.0, use ExecuteSqlCommandAsync (later versions use ExecuteSqlRawAsync)
        await _dbContext.Database.ExecuteSqlCommandAsync(sqlContent);
        await _dbContext.SaveChangesAsync();
    }
}

ASP.NET Zero uses a dedicated DbMigrator class (usually in your *.Migrator project) to run all EF Core migrations. We’ll modify this to run our script after all migrations are successfully applied:

public class YourProjectDbMigrator : AbpDbMigrator<YourProjectDbContext>
{
    private readonly PostMigrationSqlExecutor _sqlExecutor;

    public YourProjectDbMigrator(
        IConfigurationRoot configuration,
        IDbContextProvider<YourProjectDbContext> dbContextProvider,
        PostMigrationSqlExecutor sqlExecutor)
        : base(configuration, dbContextProvider)
    {
        _sqlExecutor = sqlExecutor;
    }

    // Prefer the async version for better practice
    public override async Task MigrateAsync()
    {
        // Run all EF Core migrations first
        await base.MigrateAsync();
        
        // Execute your custom SQL script
        await _sqlExecutor.ExecuteScriptAsync();
    }

    // If you need the sync version (for legacy code)
    public override void Migrate()
    {
        base.Migrate();
        _sqlExecutor.ExecuteScriptAsync().Wait();
    }
}

Since we implemented ITransientDependency, ABP will automatically register the executor with the dependency injection system. If you skip that interface, add this to your Migrator module’s ConfigureServices method:

[DependsOn(typeof(YourProjectEntityFrameworkModule), typeof(AbpZeroCoreModule))]
public class YourProjectMigratorModule : AbpModule
{
    public override void ConfigureServices(ServiceConfigurationContext context)
    {
        // Other existing config...
        context.Services.AddTransient<PostMigrationSqlExecutor>();
    }
}

• Script Versioning: If you need to run different scripts for different migration versions, add a simple AppliedSqlScripts table to your database to track which scripts have been executed. Update the executor to check this table before running new scripts.
• Script Deployment: Set your SQL file’s "Copy to Output Directory" property to "Copy if newer" so it’s included with your Migrator project when deployed.
• Error Handling: Wrap the script execution in a try-catch block if you want to handle failures gracefully, or let it throw to halt the migration process if the script is critical (e.g., required schema changes).

内容的提问来源于stack exchange，提问作者Bharat