Starting a PostgreSQL Database in Docker

https://www.postgresql.org

docker pull postgres
docker run --name postgres -e POSTGRES_PASSWORD=postgres -e TZ=Asia/Shanghai -d -p 5432:5432 postgres

Connecting to the PostgreSQL Database with PgAdmin

https://www.pgadmin.org

SHOW timezone;

Using the PostgreSQL Database with EF Core

dotnet add package Npgsql.EntityFrameworkCore.PostgreSQL

Defining the Entity Type

public class User
{
    public int Id { get; set; }
    public string UserName { get; set; }
    public string PasswordHash { get; set; }
    public DateTimeOffset CreationTime { get; set; } = DateTimeOffset.UtcNow;
}

Configuring the Entity Type

public class UserEntityTypeConfiguration : IEntityTypeConfiguration<User>
{
    public void Configure(EntityTypeBuilder<User> builder)
    {
        builder.ToTable("Users");
        builder.HasKey(x => x.Id);
        builder.Property(x => x.UserName).IsRequired().HasMaxLength(50);
        builder.Property(x => x.PasswordHash).IsRequired().HasMaxLength(50);
        builder.Property(x => x.CreationTime);
    }
}

Creating the DbContext

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

    protected override void OnModelCreating(ModelBuilder builder)
    {
        base.OnModelCreating(builder);

        builder.ApplyConfigurationsFromAssembly(Assembly.GetExecutingAssembly());

        AppContext.SetSwitch("Npgsql.EnableLegacyTimestampBehavior", true);
        AppContext.SetSwitch("Npgsql.DisableDateTimeInfinityConversions", true);
    }
}

Database Connection String

{
  "ConnectionStrings": {
    "IdentityDatabase": "Host=localhost;Port=5432;Database=IdentityService;Username=postgres;Password=postgres"
  }
}

Registering the DbContext

builder.Services.AddDbContext<IdentityServiceDbContext>(options =>
{
    options.UseNpgsql(builder.Configuration.GetConnectionString("IdentityDatabase"));
});

Migrating the Database

dotnet tool install --global dotnet-ef
dotnet add package Microsoft.EntityFrameworkCore.Design

dotnet ef migrations add InitialCreate --output-dir EntityFrameworks/Migrations
dotnet ef database update

PostgreSQL Database Naming Conventions

https://github.com/efcore/EFCore.NamingConventions

Dropping the Database and Removing Migrations

dotnet ef database drop --force
dotnet ef migrations remove

Migrating with a Script

dotnet ef migrations script

EF Core Column Migration Ordering

By default, when creating a table with migrations, EF Core orders primary key columns first, then properties of the entity type and owned types, and finally properties of the base type. However, you can specify a different column order:

protected override void OnModelCreating(ModelBuilder modelBuilder)
{
    modelBuilder.Entity<Blog>()
        .Property(b => b.Url)
        .HasColumnOrder(2);

    modelBuilder.Entity<Blog>()
        .Property(b => b.Rating)
        .HasColumnOrder(3);
}

PostgreSQL Driver Automatic Time Conversion Configuration


AppContext.SetSwitch("Npgsql.EnableLegacyTimestampBehavior", true);
AppContext.SetSwitch("Npgsql.DisableDateTimeInfinityConversions", true);