This is a very important real-time EF Core question.

Let’s break it down clearly, practically, and safely, exactly how it’s done in production systems.

---

How to Migrate in EF Core Without Data Loss

(Adding / Removing Columns Safely)

---

Golden Rule (Interview Answer)

EF Core migrations never delete data unless you explicitly tell them to.
Data loss happens only when:

• You drop a column/table

• You change data type incorrectly

• You don’t provide defaults for non-nullable columns

---

1️⃣ Adding a Column WITHOUT Data Loss (Safe Way)

Scenario

Existing Users table already has data.
Now you want to add a new column.

---

✅ Case 1: Add Nullable Column (Safest)

public string PhoneNumber { get; set; }

Migration generated

migrationBuilder.AddColumn<string>(
    name: "PhoneNumber",
    table: "Users",
    nullable: true);

✔️ No data loss
✔️ Existing rows remain untouched

👉 Recommended approach

---

✅ Case 2: Add Non-Nullable Column (Correct Way)

public bool IsActive { get; set; }

❌ This will fail if table already has rows.

✔️ Correct Solution – Provide Default Value

migrationBuilder.AddColumn<bool>(
    name: "IsActive",
    table: "Users",
    nullable: false,
    defaultValue: true);

✔️ Existing rows get default value
✔️ No data loss

---

🚫 WRONG WAY

public bool IsActive { get; set; } // no default

❌ Migration fails
❌ Causes runtime exception

---

2️⃣ Removing a Column WITHOUT Data Loss (Safe Strategy)

Important Truth

Dropping a column ALWAYS deletes data
There is no automatic safe delete.

So how do real projects do it?

---

✅ Strategy 1: Soft Remove (Recommended)

Instead of deleting column:

public bool IsDeprecated { get; set; }

✔️ Column remains
✔️ App stops using it
✔️ Data preserved

---

✅ Strategy 2: Two-Step Migration (Production Safe)

Step 1: Backup Data

SELECT OldColumn INTO Users_OldColumn_Backup FROM Users;

Step 2: Drop Column

migrationBuilder.DropColumn(
    name: "OldColumn",
    table: "Users");

✔️ No business data lost
✔️ Recoverable if needed

---

✅ Strategy 3: Move Data Before Removing Column

migrationBuilder.Sql(
    "UPDATE Users SET NewColumn = OldColumn");

Then drop old column.

✔️ Data preserved
✔️ Schema cleaned

---

3️⃣ Renaming Column (Common Data Loss Mistake)

❌ Wrong Way

public string MobileNumber { get; set; } // renamed from PhoneNumber

EF Core thinks:

• Drop PhoneNumber

• Add MobileNumber

➡️ ❌ DATA LOSS

---

✅ Correct Way – Rename Column

migrationBuilder.RenameColumn(
    name: "PhoneNumber",
    table: "Users",
    newName: "MobileNumber");

✔️ Data retained
✔️ Safe migration

---

4️⃣ Changing Column Data Type (Safely)

❌ Risky

public int Age { get; set; } // previously string

✅ Safe Migration

migrationBuilder.Sql(
    "UPDATE Users SET Age = TRY_CAST(Age AS INT)");

Then change column type.

✔️ Converts data
✔️ Prevents failure

---

5️⃣ Best Practices to Avoid Data Loss (Real-World)

✔️ Always review migration script

dotnet ef migrations script

✔️ Take DB backup before prod migration

✔️ Never use EnsureCreated() in prod

✔️ Avoid auto-migrate on app startup

✔️ Use feature-based migrations

---

6️⃣ Real Interview Answer (Concise)

To migrate without data loss in EF Core, we add nullable or default-valued columns, rename columns using RenameColumn, migrate data using SQL before dropping columns, and never delete schema elements directly in production without backup.

---

7️⃣ Quick Safety Checklist

Operation	Safe?	How
Add column	✅	Nullable or default
Rename column	✅	RenameColumn
Remove column	❌	Backup or soft delete
Change type	⚠️	Data conversion SQL
Drop table	❌	Archive first

---

If you want next:

• 🔥 Zero-downtime production migration

• 🔥 Blue-green DB migration strategy

• 🔥 EF Core migration rollback

• 🔥 Handling huge tables (millions of rows)

• 🔥 Migration interview questions

Just say the word 👍