Database migrations using Entity Framework Core

If EF Core Tools has not been installed yet, run
dotnet tool install --global dotnet-ef

Install nuget packages

In the project, install these packages
Microsoft.EntityFrameworkCore 8.0.14
Microsoft.EntityFrameworkCore.Design 8.0.14
Npgsql.EntityFrameworkCore.PostgreSQL 8.0.11

PostgreSQL

In PostgreSQL, create a database Library.

Scaffold DatabaseContext and model classes

Use scaffold to create Models folder, relevant model classes and Models\DatabaseContext.cs in Fluent API. As scaffolding regenerates DatabaseContext and model classes, it's done once and never again. Change the database name, username and password according to yours.

dotnet ef dbcontext scaffold "Host=127.0.0.1;Port=5432;Database=Library;Username=postgres;Password=;" Npgsql.EntityFrameworkCore.PostgreSQL -o Models --context DatabaseContext --force

For table names, column names, indexes, relations etc, we are using Fluent API, not Data Annotations because it has some limitations such as unable to create composite keys.

DatabaseContext

In DatabaseContext.cs, delete these methods

1. DatabaseContext() as we'll be using AddDbContextPool()
2. OnConfiguring() as it contains DB connection string

DatabaseContext should look like this

public partial class DatabaseContext : DbContext
{
    public DatabaseContext(DbContextOptions<DatabaseContext> options)
        : base(options)
    {
    }

    public virtual DbSet<Booking> Bookings { get; set; }
    
    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        OnModelCreatingPartial(modelBuilder);
    }

    partial void OnModelCreatingPartial(ModelBuilder modelBuilder);
}

Connect to database

In appsettings.Development.json, add

{
  //...
  "Database": {
    "ConnectionString": "Host=127.0.0.1;Port=5432;Database=Library;Username=root;Password=;"
  }
}

In AppSettings.cs, add Database

public class AppSettings
{
    public string Timezone { get; set; }
    public DatabaseSetting Database { get; set; } 

    public class DatabaseSetting
    {
        public string ConnectionString { get; set; }
    }
}

In Utilities folder, add DatabaseConfigurator.cs

We are using AddDbContextPool() which is more performant than AddDbContext() as it reuse previously created connections, thereby reducing the overhead of creating a new connection each time the application requests access to the database.

public static class DatabaseConfigurator
{
    public static void ConfigureDatabase(this WebApplicationBuilder builder)
    {
        var connectionString = builder.Configuration.Get<AppSettings>().Database.ConnectionString;
        if (string.IsNullOrEmpty(connectionString))
        {
            throw new Exception("Connection string is empty");
        }

        builder.Services.AddDbContextPool<DatabaseContext>((serviceProvider, options) =>
            options.UseNpgsql(connectionString)
        );
    }
}

In Program.cs, add

//...

builder.ConfigureDatabase();

builder.Services.AddControllers();

//...

Code first migrations

There are 2 approaches to create migrations, code first or database first.

We'll be using code first where we write the Models, update DatabaseContext and run dotnet ef migrations add XXX to generate migration file which will include the table changes.

In Models, add BookModel.cs

public class BookModel
{
    public int Id { get; set; }
    public string Title { get; set; }
    public string Author { get; set; }
    public int PublicationYear { get; set; }
}

Update DatabaseContext.cs.

public virtual DbSet<BookModel> Book { get; set; }

protected override void OnModelCreating(ModelBuilder modelBuilder)
{
    modelBuilder.Entity<BookModel>(entity =>
    {
        entity.ToTable("book");

        entity.Property(e => e.Id).HasColumnName("id");

        entity.Property(e => e.Title).HasColumnName("title");

        entity.Property(e => e.Author).HasColumnName("author");
        
        entity.Property(e => e.PublicationYear).HasColumnName("publication_year");
    });

    OnModelCreatingPartial(modelBuilder);
}

To create initial migration, run dotnet ef migrations add Initial
which will create Migrations/20250318132701_Initial.cs

It was able to generate this migration because it compared DatabaseContextSnapshot.cs which has no tables against DatabaseContext.cs which has Book table.

public partial class Initial : Migration
{
    /// <inheritdoc />
    protected override void Up(MigrationBuilder migrationBuilder)
    {
        migrationBuilder.CreateTable(
            name: "book",
            columns: table => new
            {
                id = table.Column<int>(type: "integer", nullable: false)
                    .Annotation("Npgsql:ValueGenerationStrategy", NpgsqlValueGenerationStrategy.IdentityByDefaultColumn),
                title = table.Column<string>(type: "text", nullable: false),
                author = table.Column<string>(type: "text", nullable: false),
                publication_year = table.Column<int>(type: "integer", nullable: false)
            },
            constraints: table =>
            {
                table.PrimaryKey("PK_book", x => x.id);
            });
    }

    /// <inheritdoc />
    protected override void Down(MigrationBuilder migrationBuilder)
    {
        migrationBuilder.DropTable(
            name: "book");
    }
}

Run migrations

To run migrations, use dotnet ef database update
which will create the database (if it does not exist), book table and __efmigrationshistory table containing list of executed migrations.

In addition, DatabaseContextModelSnapshot will be updated to contain book table. \

Rollback to specific migration

dotnet ef database update LastGoodMigration

Delete most recent migration file

dotnet ef migrations remove

Regenerate DatabaseContextSnapshot

dotnet ef migrations add temp

dotnet ef migrations remove