Generating migration scripts

dotnet ef migrations add InitialCreate --output-dir Infrastructure/Migrations

Applying the latest migration to the database manually

dotnet ef database update

Creating the database with code

await dbContext.Database.EnsureCreatedAsync();

EnsureCreatedAsync creates the database if it does not exist and creates all the tables, but it does not apply migration scripts. It is suitable for development environments.

Applying the latest migration to the database with code

await dbContext.Database.MigrateAsync();

MigrateAsync applies migration scripts and creates the database first if it does not exist. It is suitable for production environments.

Caveats

If the database was previously created with EnsureCreatedAsync, then calling MigrateAsync will throw an exception, because the database and all tables already exist and the migrations contain scripts that create those tables.

If the database does not exist, MigrateAsync will first create the database, then create the migration history table, and then apply the migration scripts. Along the way it prints some error and warning messages. They can be ignored, but it is not elegant.

A more elegant approach

This uses some EF Core internal services; note the differences in how they work.

private static async ValueTask RunMigrationAsync(TDbContext dbContext, CancellationToken cancellationToken)
{
    var strategy = dbContext.Database.CreateExecutionStrategy();
    var dbCreator = dbContext.GetService<IRelationalDatabaseCreator>();
    var historyRepository = dbContext.GetService<IHistoryRepository>();
    await strategy.ExecuteAsync(async () =>
    {
        if (!await dbCreator.ExistsAsync(cancellationToken))
        {
            await dbCreator.CreateAsync(cancellationToken);
        }
        await historyRepository.CreateIfNotExistsAsync();
        await dbContext.Database.MigrateAsync(cancellationToken);
    });
}

  1. Use dbCreator.ExistsAsync to first check whether the database exists; if it does not, create only the database (it does not create tables). At this point you have an empty database.

  2. Then use historyRepository.CreateIfNotExistsAsync to create the migration history table in the empty database to hold the migration records.

  3. Finally, use dbContext.Database.MigrateAsync to apply the migration scripts. Because the scripts run against an empty database, they perform all operations including creating the tables.

  4. After migration completes, the database contains all the tables plus the migration history table, which records the latest migration.

  5. Subsequent calls to dbContext.Database.MigrateAsync will check the migration history table and apply any new migration scripts.

  6. This achieves automatic migration and database initialization.

To run all of the above in a single transaction, we use dbContext.Database.CreateExecutionStrategy to create an execution strategy and perform the steps within that strategy.

Database seeding approaches

In the latest EF 9.0, the recommendation is to use UseSeeding and UseAsyncSeeding to seed the database with initial data; this code runs at migration time.

protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)=> optionsBuilder
        .UseSqlServer(@"Server=(localdb)\mssqllocaldb;Database=EFDataSeeding;Trusted_Connection=True;ConnectRetryCount=0")
        .UseSeeding((context, _) =>
        {
            var testBlog = context.Set<Blog>().FirstOrDefault(b => b.Url == "http://test.com");
            if (testBlog == null)
            {
                context.Set<Blog>().Add(new Blog { Url = "http://test.com" });
                context.SaveChanges();
            }
        })
        .UseAsyncSeeding(async (context, _, cancellationToken) =>
        {
            var testBlog = await context.Set<Blog>().FirstOrDefaultAsync(b => b.Url == "http://test.com", cancellationToken);
            if (testBlog == null)
            {
                context.Set<Blog>().Add(new Blog { Url = "http://test.com" });
                await context.SaveChangesAsync(cancellationToken);
            }
        });

The traditional seed data approach is not recommended because it runs on every migration and does not support asynchronous operations.


protected override void OnModelCreating(ModelBuilder modelBuilder)
{
    modelBuilder.Entity<Blog>().HasData(
        new Blog { BlogId = 1, Url = "http://sample.com" },
        new Blog { BlogId = 2, Url = "http://sample2.com" }
    );
}

Database initialization in the Zero framework

In some scenarios, database initialization can be a time-consuming operation, so we use an IHostedService or BackgroundService to run database initialization asynchronously.

public class DbInitializer : IHostedService
{
    private readonly IServiceProvider _serviceProvider;

    public DbInitializer(IServiceProvider serviceProvider)
    {
        _serviceProvider = serviceProvider;
    }

    public async Task StartAsync(CancellationToken cancellationToken)
    {
        using var scope = _serviceProvider.CreateScope();
        var dbContext = scope.ServiceProvider.GetRequiredService<AppDbContext>();
        // The code to initialize the database
    }

    public Task StopAsync(CancellationToken cancellationToken) => Task.CompletedTask;
}