CODEWITHSUNDEEP
c#sqlentity-framework
Matthew
Matthew

Reputation: 11

Including sql files when generating Migrations in EF Core - asp.net

We have a project ongoing at the moment which is using EF commands to automatically generate a migration script, i.e. dotnet ef migrations add InitialCreate

In addition to the auto-created entities, we need to run sql commands from files to generate stored procedures, which are required by external services.

It is simple enough to add the following manually to the migration, and this works locally:

var sqlFiles =
    Directory.GetFiles(
        $"{AppDomain.CurrentDomain.BaseDirectory}/sql", "*.sql");
foreach (var sqlFile in sqlFiles)
{
    migrationBuilder.Sql(File.ReadAllText(sqlFile));
}

However this needs adding in again every time we run the migration builder (which is fairly frequently as there are often new models to add etc.), and this doesn't work when pushing to our development servers as they automatically run the migration builder and database update commands.

Is there a way to get EF to automatically include the sql files when generating the migration? Or a better way of doing this in general?

Upvotes: 1

Views: 1986

Answers (1)

Stuart Frankish
Stuart Frankish

Reputation: 868

This is our implementation for executing SQL Scripts on app startup which might useful or at least guide you in some direction. This is executed from program.cs as part of the Main() method:

public static void Main(string[] args)
{
    var host = CreateWebHostBuilder(args).Build();
    ExecuteAzureDatabaseScripts(host);
    host.Run();
}


private static void ExecuteAzureDatabaseScripts(IWebHost host)
{
    var scopeFactory = host.Services.GetService<IServiceScopeFactory>();
    using (var scope = scopeFactory.CreateScope())
    {
        var hostingEnvironment = scope.ServiceProvider.GetService<IHostingEnvironment>();

        if (hostingEnvironment.IsDevelopment()) return;

        var scriptRunner = scope.ServiceProvider.GetService<SqlScriptRunner>();
        var storeContext = scope.ServiceProvider.GetService<IStoreDbContext>();
        scriptRunner.Context = storeContext;
        scriptRunner.Directory = @"SqlScripts\Azure";
        scriptRunner.ExecuteScripts();
    }
}

ScriptRunner class:

public class SqlScriptRunner
{
    private IDbContext _context;
    private readonly IHostingEnvironment _hosting;
    private string _directory;

    public SqlScriptRunner(IHostingEnvironment hosting)
    {
        _hosting = hosting;
    }

    public IDbContext Context
    {
        set => _context = value;
    }

    /// <summary>
    /// Name of the directory containing json seed data e.g. Data\Store
    /// </summary>
    public string Directory
    {
        set => _directory = value;
    }

    public void ExecuteScripts()
    {
        if (_context == null) throw new NullReferenceException("Database context for SqlScriptRunner is null.");
        if (string.IsNullOrEmpty(_directory)) throw new NullReferenceException("SqlScriptRunner directory is null or empty.");

        var directoryInfo = new DirectoryInfo( Path.Combine(_hosting.ContentRootPath, _directory));

        foreach (var file in directoryInfo.GetFiles().OrderBy(f => f.FullName))
        {
            var script = File.ReadAllText(file.FullName);
            _context.Database.ExecuteSqlCommand(script);
        }
    }
}

Upvotes: 1

Related Questions