Code-First vs Database-First Approaches

When starting an EF Core project, you must make a critical architectural decision: Are you going to master the database schema from within your C# Code, or are you going to let a DBA design the Database graphically while your C# code merely reacts to the changes?

1. Code-First (The Modern Standard)

In the Code-First paradigm, the physical SQL database does not exist initially. You write raw C# classes. EF Core reads those classes, authors physical SQL Scripts called Migrations, and uses those scripts to generate the database schema automatically.

// 1. Write the class in C#
public class Author {
    public int Id { get; set; }
    public string Name { get; set; }
}

// 2. Open terminal and generate the Migration script
// dotnet ef migrations add InitialCreate

// 3. Command EF Core to build the database!
// dotnet ef database update

• Pros: C# developers hold total control. Schema versions are perfectly tracked in Git via the Migration files. Excellent testability.
• Cons: Requires developers to understand relational limits. Poor manual adjustments by DBAs will get overwritten by the next code migration.

2. Database-First (The Legacy/Enterprise Reality)

In heavily structured enterprises, strict DBAs manage the SQL Server. They create tables, triggers, and highly optimized indexes via SQL Scripts. C# developers are absolutely forbidden from altering tables via Migrations. Instead, we use Scaffolding to inspect the physical database and generate C# classes from the tables.

dotnet ef dbcontext scaffold "Server=...;" Microsoft.EntityFrameworkCore.SqlServer -o Models

• Pros: Masterful DBAs can utilize advanced SQL features that Migrations struggle with. Great for attaching to massive legacy 20-year-old databases.
• Cons: Cumbersome workflow. You must run the scaffold command every time a table changes. If you manually edited a generated class, it gets overwritten on the next scaffold.

3. Interview Mastery

Q: "We are using Code-First. I added a new property 'public string Password { get; set; }' to the User class, but I forgot to run 'dotnet ef migrations add' before launching the application. When my C# code attempts to query the database, what exactly happens?"

Architect Answer: "The application will immediately crash with a 'SqlException: Invalid column name Password'. At startup, EF Core reads your C# classes and creates an internal mapping model. It completely assumes the physical SQL Database structure perfectly matches this internal model model. When you query the users, EF Core generates the query: 'SELECT Id, Name, Password FROM Users'. Because you never migrated the database, the column physically doesn't exist in SQL Server, and the query violently fails. This highlights why strict CI/CD pipelines use the EF Core CLI bundle tools to verify and automatically apply migrations during deployment before the web server even boots up."