Cloud SQL 建立好了一個 PostgreSQL 但目前裡面都還沒有 Table Schema ，先前也設計好 Schema

為了方便做資料庫的版本控制，我們使用 Core First 的方法來建立 Table，

建立 Model

首先要三個表建立 Model

Data/Model/Event.cs

using System.ComponentModel.DataAnnotations.Schema;

namespace iThome2024.ProcessService.Data.Model;

public class Event
{
    [DatabaseGenerated(DatabaseGeneratedOption.Identity)]
    public int Id { get; set; }
    [Column(TypeName = "varchar(200)")]
    public string Name { get; set; }
    [Column(TypeName = "timestamp")]
    public DateTime EventDate { get; set; }
    [Column(TypeName = "timestamp")]
    public DateTime StartSalesDate { get; set; }
    [Column(TypeName = "timestamp")]
    public DateTime EndSalesDate { get; set; }
    [Column(TypeName = "varchar(2000)")]
    public string? Description { get; set; }
    [Column(TypeName = "varchar(500)")]
    public string? Remark { get; set; }
    public List<Seat> Seats { get; } = new();

}

Date/Model/Seat.cs

using System.ComponentModel.DataAnnotations.Schema;

namespace iThome2024.ProcessService.Data.Model;

public class Seat
{
    [DatabaseGenerated(DatabaseGeneratedOption.Identity)]
    public int Id { get; set; }
    public int EventId { get; set; }
    [Column(TypeName = "varchar(50)")]
    public string? Area { get; set; }
    [Column(TypeName = "varchar(200)")]
    public string Name { get; set; }
    public int Status { get; set; }
    public Event Event { get; set; }
}

Date/Model/User.cs

using System.ComponentModel.DataAnnotations.Schema;

namespace iThome2024.ProcessService.Data.Model;

public class User
{
    [DatabaseGenerated(DatabaseGeneratedOption.Identity)]
    public int Id { get; set; }
    [Column(TypeName = "varchar(200)")]
    public string? Username { get; set; }
    [Column(TypeName = "varchar(200)")]
    public string? Password { get; set; }
    [Column(TypeName = "timestamp")]
    public DateTime CreateTime { get; set; }
}

更新 DBContext

再來更新 DBContext 加入剛剛建立的 Model

using System.ComponentModel.DataAnnotations.Schema;
using iThome2024.ProcessService.Data.Model;
using Microsoft.EntityFrameworkCore;

namespace iThome2024.ProcessService.Data;

public class TicketSalesContext : DbContext
{
    public DbSet<User> User { get; set; }
    public DbSet<Event> Event { get; set; }
    public DbSet<Seat> Seat { get; set; }

    public TicketSalesContext(DbContextOptions<TicketSalesContext> options) : base(options)
    {
    }
}

執行 Migration

安裝 EFCore tool

dotnet tool install --global dotnet-ef

專案加入 Design 套件

dotnet add package Microsoft.EntityFrameworkCore.Design

使用 ef tool 建立 migration 用的程式碼

dotnet ef migrations add InitialCreate

執行 migration ，注意執行時要確保 Clous SQL Auth Proxy 是有開啟的

dotnet ef database update

確認 Migration 結果

使用 IDE 連線至 postgresql 就可以看到剛剛建立的三個表格，以及儲存 migration 的表格