Database First with an Existing Database

Code First or Database First

Up to now this course has used Code First. You wrote Book and Author in C#, and migrations built the tables from them.

APIs are often built on top of a database that already exists. A company database that other apps share, a schema a DBA owns, or a legacy system you are putting an API in front of. You are not allowed to change those tables from your code. You need C# classes that match them.

That is Database First. EF Core connects to the database, reads its tables, columns, keys and foreign keys, and writes the entity classes and the DbContext for you. This is called scaffolding or reverse engineering.

After scaffolding, nothing else changes. CRUD Operations with EF Core, LINQ, the change tracker and SaveChangesAsync all work the same way.

The existing database

Here is the schema the library team already runs in SQL Server:

CREATE TABLE Authors (
    AuthorId INT IDENTITY(1,1) CONSTRAINT PK_Authors PRIMARY KEY,
    FullName NVARCHAR(120) NOT NULL
);

CREATE TABLE Books (
    BookId    INT IDENTITY(1,1) CONSTRAINT PK_Books PRIMARY KEY,
    Title     NVARCHAR(200) NOT NULL,
    Isbn      NCHAR(13) NULL,
    Price     DECIMAL(10, 2) NOT NULL,
    Published DATE NULL,
    AuthorId  INT NOT NULL
        CONSTRAINT FK_Books_Authors REFERENCES Authors (AuthorId)
);

Tools and packages

The same ones from Entity Framework Core Setup:

dotnet add package Microsoft.EntityFrameworkCore.SqlServer
dotnet add package Microsoft.EntityFrameworkCore.Design
dotnet tool install --global dotnet-ef

Then put the connection string in appsettings.json (or in user secrets if it has a password):

{
  "ConnectionStrings": {
    "Default": "Server=(localdb)\\mssqllocaldb;Database=LibraryDb;Trusted_Connection=True;TrustServerCertificate=True"
  }
}

Scaffold it

Run this in the API project folder:

dotnet ef dbcontext scaffold Name=ConnectionStrings:Default Microsoft.EntityFrameworkCore.SqlServer --output-dir Entities --context-dir Data --context LibraryContext --no-onconfiguring

What each part does:

• Name=ConnectionStrings:Default reads the connection string from configuration, so it never appears in the command or the code.
• Microsoft.EntityFrameworkCore.SqlServer is the provider. Use Npgsql.EntityFrameworkCore.PostgreSQL or Microsoft.EntityFrameworkCore.Sqlite for those databases.
• --output-dir Entities puts the entity classes in their own folder, away from your DTOs.
• --context-dir Data --context LibraryContext names the context and its folder.
• --no-onconfiguring skips the OnConfiguring method. You register the context in Program.cs, so it is not needed.

You get:

Data/
  LibraryContext.cs
Entities/
  Author.cs
  Book.cs

The generated code

public partial class Book
{
    public int BookId { get; set; }

    public string Title { get; set; } = null!;

    public string? Isbn { get; set; }

    public decimal Price { get; set; }

    public DateOnly? Published { get; set; }

    public int AuthorId { get; set; }

    public virtual Author Author { get; set; } = null!;
}

Nullable columns become nullable types (string?, DateOnly?). DATE maps to DateOnly. The foreign key gets an Author navigation, and Author gets a Books collection.

The context keeps the column details:

public partial class LibraryContext : DbContext
{
    public LibraryContext(DbContextOptions<LibraryContext> options)
        : base(options)
    {
    }

    public virtual DbSet<Author> Authors { get; set; }

    public virtual DbSet<Book> Books { get; set; }

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        modelBuilder.Entity<Book>(entity =>
        {
            entity.Property(e => e.Isbn)
                .HasMaxLength(13)
                .IsFixedLength();
            entity.Property(e => e.Price).HasColumnType("decimal(10, 2)");
            entity.Property(e => e.Title).HasMaxLength(200);

            entity.HasOne(d => d.Author).WithMany(p => p.Books)
                .HasForeignKey(d => d.AuthorId)
                .OnDelete(DeleteBehavior.ClientSetNull)
                .HasConstraintName("FK_Books_Authors");
        });

        OnModelCreatingPartial(modelBuilder);
    }

    partial void OnModelCreatingPartial(ModelBuilder modelBuilder);
}

Add --data-annotations to the command if you want [Key], [StringLength] and similar attributes on the classes instead. Settings that have no attribute stay in OnModelCreating.

Register it and return DTOs

Registration is the same as Code First:

builder.Services.AddDbContext<LibraryContext>(options =>
    options.UseSqlServer(builder.Configuration.GetConnectionString("Default")));

In an API there is one extra rule that matters a lot with scaffolded classes: do not return the entities directly. Book points to Author, and Author has a list of Book. If you load both and return them, System.Text.Json follows the loop and throws “A possible object cycle was detected”. It would also expose every column the DBA ever adds.

Project into a DTO instead:

public record BookDto(int Id, string Title, decimal Price, string Author);

[ApiController]
[Route("api/books")]
public class BooksController : ControllerBase
{
    private readonly LibraryContext _db;

    public BooksController(LibraryContext db) => _db = db;

    [HttpGet]
    public async Task<IEnumerable<BookDto>> GetAll() =>
        await _db.Books
            .AsNoTracking()
            .OrderBy(b => b.Title)
            .Select(b => new BookDto(b.BookId, b.Title, b.Price, b.Author.FullName))
            .ToListAsync();
}

Select turns into a SQL query that reads only those four columns, with the join done in the database. Your API contract now stays the same even when the table grows.

Only the tables you need

Use --table once per table, or --schema for a whole schema:

dotnet ef dbcontext scaffold Name=ConnectionStrings:Default Microsoft.EntityFrameworkCore.SqlServer --output-dir Entities --context-dir Data --context LibraryContext --no-onconfiguring --table Books --table Authors

This keeps the context small when the database is shared with many other systems.

When the schema changes

The DBA adds a PageCount column. Run the same command again with --force:

dotnet ef dbcontext scaffold Name=ConnectionStrings:Default Microsoft.EntityFrameworkCore.SqlServer --output-dir Entities --context-dir Data --context LibraryContext --no-onconfiguring --force

That is the whole Database First loop: the database changes first, then you re-scaffold. There are no migrations, because your code never changes the schema.

--force overwrites the generated files completely, so any edit inside them is lost. Save the full command as a script in the repo, such as scaffold.ps1, so the whole team uses the same options every time.

Extending generated classes

The generated classes are partial. Add your own members in a separate file with the same class name and namespace:

// Entities/Partials/Book.cs (yours, never overwritten)
public partial class Book
{
    public bool IsClassic => Published is { } date && date.Year < 1950;
}

A property with only a getter is not mapped, so EF Core ignores it.

Extra model configuration goes in the OnModelCreatingPartial hook, written in your own partial context file:

// Data/LibraryContext.Custom.cs (yours, never overwritten)
public partial class LibraryContext
{
    partial void OnModelCreatingPartial(ModelBuilder modelBuilder)
    {
        modelBuilder.Entity<Book>()
            .HasIndex(b => b.Isbn)
            .HasDatabaseName("IX_Books_Isbn");
    }
}

Re-scaffolding only overwrites the files it generates, so both of these survive.

The context in another project

In a larger solution, the context often lives in a class library such as Library.Data. Point the tool at both projects. The startup project is where the configuration and connection string live:

dotnet ef dbcontext scaffold Name=ConnectionStrings:Default Microsoft.EntityFrameworkCore.SqlServer --project ../Library.Data --startup-project . --output-dir Entities --context-dir . --context LibraryContext --no-onconfiguring

The Design package must be installed in the startup project.

Moving to migrations later

If your team takes over the schema for good, you can switch to Code First. Create a first migration and empty it, because the tables already exist:

dotnet ef migrations add InitialCreate

protected override void Up(MigrationBuilder migrationBuilder)
{
    // Baseline: these tables already exist in the database.
}

protected override void Down(MigrationBuilder migrationBuilder)
{
}

dotnet ef database update

This only records InitialCreate in __EFMigrationsHistory. From then on, change the classes and add migrations, and stop scaffolding. Never do both at once. If anyone still changes the schema by hand, stay with Database First.

Common problems

“Build failed.” The tool compiles the project before scaffolding. Fix any compile error, including in your partial files.

“A possible object cycle was detected.” You returned scaffolded entities with their navigation properties loaded. Return DTOs.

“Unable to find provider assembly.” The provider package is not installed in the startup project.

Some tables are missing. Tables without a primary key are skipped with a warning. If you only read from such a table or view, map it with HasNoKey() in OnModelCreatingPartial.

Edits disappeared after re-scaffolding. They were inside a generated file. Move them into a partial file.

Quick recap

• Database First generates C# from the database. Code First generates the database from C#.
• dotnet ef dbcontext scaffold writes the entity classes and the DbContext.
• Use Name=ConnectionStrings:Default and --no-onconfiguring to keep connection strings out of code.
• In an API, return DTOs, never the scaffolded entities.
• Re-scaffold with --force when the schema changes, and keep a script for the command.
• Put your own code in separate partial files and in OnModelCreatingPartial.
• Use --project and --startup-project when the context lives in a class library.
• You can switch to migrations later with an empty baseline migration.