Entity Framework Core: Database First Approach

1. Overview

In the Database First approach, the database is the "Source of Truth." You design and modify your schema using SQL tools (like SSMS or Azure Data Studio), and EF Core "reverse engineers" (scaffolds) that schema into C# Entity classes and a DbContext.

Ideal For:

• Legacy applications with existing databases.

• Environments where DBAs control the schema.

• Scenarios where the database is shared by multiple applications.

---

2. Setup and Installation

A. Prerequisites

Ensure the .NET SDK is installed. You must also install the global EF Core CLI tool:

dotnet tool install --global dotnet-ef

B. NuGet Packages

Install the following packages in your project (e.g., via the Package Manager Console or Terminal). Assuming you are using SQL Server:

dotnet add package Microsoft.EntityFrameworkCore.SqlServer
dotnet add package Microsoft.EntityFrameworkCore.Tools
dotnet add package Microsoft.EntityFrameworkCore.Design

• SqlServer: The database provider.

• Tools: Enables the scaffold command.

• Design: Required for the scaffolding engine to run.

---

3. Scaffolding (Generating the Code)

The core operation of Database First is Scaffolding. This process reads the database schema and generates the C# code.

The Basic Command

Run this in your project's root folder:

dotnet ef dbcontext scaffold "Server=myServer;Database=MyDb;Trusted_Connection=True;TrustServerCertificate=True;" Microsoft.EntityFrameworkCore.SqlServer --output-dir Models --context AppDbContext

Command Parameters Explained

---

4. Managing the Code Lifecycle

A. Handling Database Updates

Since the database is the source of truth, you cannot modify the generated C# classes directly to add columns.

1. Modify the Database: Add a column IsActive to the Users table in SQL.

2. Re-Scaffold: Run the scaffold command again with the --force flag.

    dotnet ef dbcontext scaffold "..." ... --force
   

   • Warning: This deletes and recreates your entity files. Any code you wrote inside User.cs will be lost.

B. Extending Entities (Partial Classes)

To add custom logic or properties without losing them during re-scaffolding, use Partial Classes. EF Core generates all entities as partial.

File 1: Models/User.cs (Auto-Generated - DO NOT TOUCH)

public partial class User 
{
    public int Id { get; set; }
    public string Name { get; set; }
}

File 2: Extensions/UserExtended.cs (Your Custom File)

public partial class User 
{
    // Computed property (not in DB)
    public string DisplayName => $"User: {Name}";

    // Method
    public bool IsValid() => !string.IsNullOrEmpty(Name);
}

---

5. Exceptions, Issues & Troubleshooting

The scaffolding engine is literal. It maps exactly what it sees. If the database lacks definition, the code will lack definition.

A. Missing Primary Keys (Runtime Error)

EF Core requires a Primary Key to track entities.

• Symptom: ModelValidationException: The entity type 'Log' requires a primary key...

• Cause: The SQL table usually defaults to a heap (no PK).

• Solution 1 (Best): Add a PK in the database and re-scaffold.

• Solution 2 (Workaround): If the table is truly read-only (like a log), configure it as Keyless in OnModelCreating:

    modelBuilder.Entity<Log>().HasNoKey();
  

B. Bad Naming Conventions

Legacy databases often use abbreviations (cst_nm, t_prdct).

• Result: ugly C# properties: public string cst_nm { get; set; }.

• Solution: Use EF Core Power Tools (see Section 6) to set up renaming rules, so cst_nm maps to CustomerName permanently.

C. Missing Relationships

• Symptom: You have Order.CustomerId (int) but no Order.Customer (object) navigation property.

• Cause: The database table has the column, but lacks a formal Foreign Key Constraint.

• Solution: You must add the Foreign Key constraint in the SQL database. EF Core only scaffolds relationships that physically exist in the schema definition.

---

6. Recommended Tooling: EF Core Power Tools

For professional development, the CLI command is often insufficient.

EF Core Power Tools is a Visual Studio extension that provides a GUI for Database First.

• Benefits:

  • Config Persistence: Saves your choices (tables selected, naming rules) in a .json file so you don't have to re-type the command.

  • Renaming: Allows you to map ugly DB column names to clean C# property names via a GUI.

  • Type Mapping: Lets you override types (e.g., force SQL date to C# DateOnly).

---

7. Security Warning

When you run the scaffold command, EF Core generates an OnConfiguring method inside your DbContext containing your connection string in plain text.

// AUTO GENERATED - SECURITY RISK
protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
    => optionsBuilder.UseSqlServer("Server=...;Password=Secret;");

Action Required:

1. Delete the connection string from OnConfiguring.

2. Move the connection string to appsettings.json.

3. Inject it via Dependency Injection in Program.cs (just like in Code First).

// Program.cs
builder.Services.AddDbContext<AppDbContext>(options => 
    options.UseSqlServer(builder.Configuration.GetConnectionString("DefaultConnection")));