📑 Migration: RemoveCityAndAddColumnsToCounty


📋 Table of Contents

  1. Overview & Purpose
  2. Dependencies & Technologies
  3. Migration Structure
  4. Schema Changes
  5. Code Snippets
  6. UML Diagrams
  7. Usage Examples
  8. Best Practices & Tips
  9. Conclusion

🌟 1. Overview & Purpose

This Entity Framework Core migration performs the following high‑level tasks:

  • Drops the existing City table in the irby schema.
  • Renames the County table into the irby schema (if not already).
  • Renames the Name column in County to ZipCode.
  • Adds a set of geospatial and contact‑related columns to the County table:
    • AreaCode
    • CountyName
    • Latitude
    • Longitude
    • StateCode
    • TimeZone

This migration centralizes all location data into one table (County) by removing the separate City construct, while enriching County with additional attributes required for the application’s geolocation and telephony functionality.


🛠️ 2. Dependencies & Technologies

  • .NET 6+ / .NET Core
  • EntityFrameworkCore (Microsoft.EntityFrameworkCore)
  • EF Core Migrations (Microsoft.EntityFrameworkCore.Migrations)
  • SQL Server (assumed by nvarchar(max) and uniqueidentifier types)

🏗️ 3. Migration Structure

public partial class RemoveCityAndAddColumnsToCounty : Migration
{
    protected override void Up(MigrationBuilder migrationBuilder)    { /* … */ }
    protected override void Down(MigrationBuilder migrationBuilder)  { /* … */ }
}

🔼 Up Method (Apply Changes)

  • Drops City
  • Moves/ensures County in the irby schema
  • Renames Name → ZipCode
  • Adds six new columns

🔽 Down Method (Revert Changes)

  • Removes the six new columns
  • Renames ZipCode → Name
  • Moves County back to default schema
  • Re-creates City table

🔄 4. Schema Changes

4.1 Dropped Entities

Schema Table Notes
irby City Entire table is removed in Up.

4.2 Renamed Entities & Columns

Action Schema Table Old Name New Name
Rename Table irby County (none) County
Rename Column irby County Name ZipCode

4.3 Added Columns to irby.County

Column Type Nullable Description
AreaCode nvarchar(max) Yes Telephone area code
CountyName nvarchar(max) Yes Human‑readable county name
Latitude nvarchar(max) Yes Geographic latitude (string)
Longitude nvarchar(max) Yes Geographic longitude (string)
StateCode nvarchar(max) Yes Two‑letter state code (e.g., CA)
TimeZone nvarchar(max) Yes Time zone identifier (e.g., PST)

4.4 Re-created Entities (Down)

Reverts the drop of City by recreating:

Schema Table Columns PK
irby City CityId (GUID), Name (nvarchar), State CityId

💻 5. Code Snippets

Up Migration

protected override void Up(MigrationBuilder migrationBuilder)
{
    // 1️⃣ Drop City table
    migrationBuilder.DropTable(name: "City", schema: "irby");

    // 2️⃣ Ensure County is in 'irby' schema
    migrationBuilder.RenameTable(name: "County", newName: "County", newSchema: "irby");

    // 3️⃣ Rename Name -> ZipCode
    migrationBuilder.RenameColumn(
        name: "Name", schema: "irby", table: "County", newName: "ZipCode");

    // 4️⃣ Add new location & telephony columns
    migrationBuilder.AddColumn<string>(name: "AreaCode",  schema: "irby", table: "County", type: "nvarchar(max)", nullable: true);
    migrationBuilder.AddColumn<string>(name: "CountyName",schema: "irby", table: "County", type: "nvarchar(max)", nullable: true);
    migrationBuilder.AddColumn<string>(name: "Latitude",  schema: "irby", table: "County", type: "nvarchar(max)", nullable: true);
    migrationBuilder.AddColumn<string>(name: "Longitude", schema: "irby", table: "County", type: "nvarchar(max)", nullable: true);
    migrationBuilder.AddColumn<string>(name: "StateCode", schema: "irby", table: "County", type: "nvarchar(max)", nullable: true);
    migrationBuilder.AddColumn<string>(name: "TimeZone",  schema: "irby", table: "County", type: "nvarchar(max)", nullable: true);
}

Down Migration

protected override void Down(MigrationBuilder migrationBuilder)
{
    // 1️⃣ Remove newly added columns
    migrationBuilder.DropColumn(name: "AreaCode",   schema: "irby", table: "County");
    migrationBuilder.DropColumn(name: "CountyName", schema: "irby", table: "County");
    migrationBuilder.DropColumn(name: "Latitude",   schema: "irby", table: "County");
    migrationBuilder.DropColumn(name: "Longitude",  schema: "irby", table: "County");
    migrationBuilder.DropColumn(name: "StateCode",  schema: "irby", table: "County");
    migrationBuilder.DropColumn(name: "TimeZone",   schema: "irby", table: "County");

    // 2️⃣ Rename column ZipCode -> Name
    migrationBuilder.RenameColumn(name: "ZipCode", table: "County", newName: "Name");

    // 3️⃣ Move County back to default schema
    migrationBuilder.RenameTable(name: "County", schema: "irby", newName: "County");

    // 4️⃣ Re-create City table
    migrationBuilder.CreateTable(
        name: "City", schema: "irby",
        columns: table => new
        {
            CityId = table.Column<Guid>(type: "uniqueidentifier", nullable: false),
            Name   = table.Column<string>(type: "nvarchar(max)", nullable: true),
            State  = table.Column<string>(type: "nvarchar(max)", nullable: true)
        },
        constraints: table =>
        {
            table.PrimaryKey("PK_City", x => x.CityId);
        });
}

🖼️ 6. UML Diagrams

erDiagram
    %% Before Migration %%
    City {
      GUID CityId PK
      string Name
      string State
    }
    County {
      GUID CountyId PK
      string Name
      ...
    }

    %% After Migration %%
    County {
      GUID CountyId PK
      string ZipCode
      string AreaCode
      string CountyName
      string Latitude
      string Longitude
      string StateCode
      string TimeZone
    }

🚀 7. Usage Examples

  1. Add Migration
    dotnet ef migrations add RemoveCityAndAddColumnsToCounty
    
  2. Apply to Database
    dotnet ef database update
    
  3. Verify Schema
    SELECT * FROM irby.County;
    -- Confirm columns: ZipCode, AreaCode, CountyName, Latitude, Longitude, StateCode, TimeZone
    

✅ 8. Best Practices & Tips

  • Always backup your database before running destructive migrations (e.g., DropTable).
  • Use HasColumnType(...) in your DbContext model configuration if you need more control over SQL types.
  • Consider splitting large migrations into smaller, atomic ones for easier troubleshooting.
  • Use nullable columns cautiously; enforce NOT NULL where data is guaranteed.
  • Validate downstream code (repositories, services) to ensure they reference the new column names (ZipCode, CountyName).

🏁 9. Conclusion

This migration consolidates geographical and contact information into a single County entity by removing the redundant City table and enriching County with several new fields. It exemplifies a typical EF Core schema refactoring: renaming, dropping, and adding columns in an Idempotent fashion—thanks to the clear separation of Up() and Down() methods.

Happy migrating! 🎉