📑 Migration: RemoveCityAndAddColumnsToCounty
📋 Table of Contents
- Overview & Purpose
- Dependencies & Technologies
- Migration Structure
- Schema Changes
- Code Snippets
- UML Diagrams
- Usage Examples
- Best Practices & Tips
- Conclusion
🌟 1. Overview & Purpose
This Entity Framework Core migration performs the following high‑level tasks:
- Drops the existing
Citytable in theirbyschema. - Renames the
Countytable into theirbyschema (if not already). - Renames the
Namecolumn inCountytoZipCode. - Adds a set of geospatial and contact‑related columns to the
Countytable:AreaCodeCountyNameLatitudeLongitudeStateCodeTimeZone
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)anduniqueidentifiertypes)
🏗️ 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
Countyin theirbyschema - Renames
Name→ZipCode - Adds six new columns
🔽 Down Method (Revert Changes)
- Removes the six new columns
- Renames
ZipCode→Name - Moves
Countyback to default schema - Re-creates
Citytable
🔄 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
- Add Migration
dotnet ef migrations add RemoveCityAndAddColumnsToCounty - Apply to Database
dotnet ef database update - 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 NULLwhere 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! 🎉