# 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:

```bash
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:

```bash
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:

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

### Command Parameters Explained

| **Parameter** | **Description** |
| --- | --- |
| `"Connection String"` | The connection string to your database. **Wrap in quotes.** |
| `Provider` | The EF Core provider (e.g., `Microsoft.EntityFrameworkCore.SqlServer`). |
| `--output-dir` | The folder where Entity classes will be generated (e.g., `Models` or `Entities`). |
| `--context-dir` | (Optional) Folder for the DbContext file (defaults to output-dir). |
| `--context` | The name of the `DbContext` class (e.g., `AppDbContext`). |
| `--force` | **Crucial:** Overwrites existing files. Required when updating the schema. |
| `--data-annotations` | Uses attributes (`[Key]`, `[Required]`) instead of Fluent API where possible. |
| `--table` / `-t` | Scaffolds only specific tables (e.g., `-t Users -t Products`). |

---

## 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.
    
    ```bash
    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)

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

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

```bash
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`:
    
    ```bash
    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**.

```bash
// 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).
    

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