EF Core Database First: Scaffold a DB Context from an Existing Database

Stop writing models by hand - let EF Core do it for you.

Connect with me:

Want to sponsor this newsletter? Let’s work together →

Introduction

Most tutorials start with a fresh database that you design yourself. But in the real world, you inherit databases. Legacy systems, shared enterprise databases, third-party schemas - they’re everywhere, and you need to integrate your .NET Web API with them fast.

The EF Core Database First approach with scaffolding is exactly the tool for this. One command generates your DbContext and all entity models - with relationships, configurations, and Fluent API mappings - directly from the existing database schema.

In this post you’ll see the full workflow: from setting up a new .NET Web API, running the scaffold command against a real SQL Server database, understanding what gets generated and why, cleaning up the output, and wiring everything into your app properly.

🎬 Watch the full video here:

---

The Starting Point: An Existing Database

For this demo, the target is an ECommerceDB SQL Server database with multiple tables, foreign key relationships, and a realistic data model - orders, customers, products, categories, order details, and shippings.

The database gets created and seeded upfront using two SQL scripts run directly in SSMS. Once the data is in place, the goal is to integrate this schema into a .NET Web API without writing a single entity class by hand.

---

Required NuGet Packages

Before running the scaffold command, three NuGet packages need to be added to your project:

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

• Tools - provides the EF Core CLI commands

• Design - required at design time for scaffolding and migrations

• SqlServer - the database provider for Microsoft SQL Server

---

The Scaffold Command

With packages in place, run this single command to generate everything:

dotnet ef dbcontext scaffold \
  "Server=localhost,1433;Database=ECommerceDB;User Id=sa;Password=YourPassword1!;TrustServerCertificate=True" \
  Microsoft.EntityFrameworkCore.SqlServer \
  --output-dir Entities

• The first argument is the full connection string to your existing database

• Microsoft.EntityFrameworkCore.SqlServer is the provider

• --output-dir Entities places all generated files into an Entities folder

---

Understanding the Generated DbContext

This is what EF Core actually generates for the ECommerceDB schema. Read through it carefully - every section tells you something important about your database.

using Microsoft.EntityFrameworkCore;

namespace ECommerceAPI.Entities;

public partial class ECommerceDbContext : DbContext
{
    public ECommerceDbContext()
    {
    }

    public ECommerceDbContext(DbContextOptions<ECommerceDbContext> options)
        : base(options)
    {
    }

    public virtual DbSet<Category> Categories { get; set; }
    public virtual DbSet<Customer> Customers { get; set; }
    public virtual DbSet<Order> Orders { get; set; }
    public virtual DbSet<OrderDetail> OrderDetails { get; set; }
    public virtual DbSet<Product> Products { get; set; }
    public virtual DbSet<Shipping> Shippings { get; set; }

    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
    {
        // WARNING: Move this connection string to appsettings.json
        optionsBuilder.UseSqlServer(
            "Server=localhost,1433;Database=ECommerceDB;User Id=sa;Password=YourPassword1!;TrustServerCertificate=True");
    }

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        modelBuilder.Entity<Category>(entity =>
        {
            entity.HasKey(e => e.Id).HasName("PK__Categori__3214EC07...");

            entity.Property(e => e.Name)
                .IsRequired()
                .HasMaxLength(100)
                .IsUnicode(false);
        });

        modelBuilder.Entity<Customer>(entity =>
        {
            entity.HasKey(e => e.Id).HasName("PK__Customer__3214EC07...");

            entity.Property(e => e.FirstName)
                .IsRequired()
                .HasMaxLength(100)
                .IsUnicode(false);
            entity.Property(e => e.LastName)
                .IsRequired()
                .HasMaxLength(100)
                .IsUnicode(false);
            entity.Property(e => e.Email)
                .IsRequired()
                .HasMaxLength(200)
                .IsUnicode(false);
        });

        modelBuilder.Entity<Order>(entity =>
        {
            entity.HasKey(e => e.Id).HasName("PK__Orders__3214EC07...");

            entity.Property(e => e.OrderDate).HasColumnType("datetime");
            entity.Property(e => e.TotalAmount).HasColumnType("decimal(18,2)");
            entity.Property(e => e.Status)
                .HasMaxLength(50)
                .IsUnicode(false);

            entity.HasOne(d => d.Customer)
                .WithMany(p => p.Orders)
                .HasForeignKey(d => d.CustomerId)
                .OnDelete(DeleteBehavior.ClientSetNull)
                .HasConstraintName("FK_Orders_Customers");
        });

        modelBuilder.Entity<OrderDetail>(entity =>
        {
            entity.HasKey(e => e.Id).HasName("PK__OrderDet__3214EC07...");

            entity.Property(e => e.UnitPrice).HasColumnType("decimal(18,2)");

            entity.HasOne(d => d.Order)
                .WithMany(p => p.OrderDetails)
                .HasForeignKey(d => d.OrderId)
                .OnDelete(DeleteBehavior.ClientSetNull)
                .HasConstraintName("FK_OrderDetails_Orders");

            entity.HasOne(d => d.Product)
                .WithMany(p => p.OrderDetails)
                .HasForeignKey(d => d.ProductId)
                .OnDelete(DeleteBehavior.ClientSetNull)
                .HasConstraintName("FK_OrderDetails_Products");
        });

        modelBuilder.Entity<Product>(entity =>
        {
            entity.HasKey(e => e.Id).HasName("PK__Products__3214EC07...");

            entity.Property(e => e.ProductName)
                .IsRequired()
                .HasMaxLength(200)
                .IsUnicode(false);
            entity.Property(e => e.Price).HasColumnType("decimal(18,2)");

            entity.HasOne(d => d.Category)
                .WithMany(p => p.Products)
                .HasForeignKey(d => d.CategoryId)
                .OnDelete(DeleteBehavior.ClientSetNull)
                .HasConstraintName("FK_Products_Categories");
        });

        modelBuilder.Entity<Shipping>(entity =>
        {
            entity.HasKey(e => e.Id).HasName("PK__Shipping__3214EC07...");

            entity.Property(e => e.ShippedDate).HasColumnType("datetime");
            entity.Property(e => e.Address)
                .HasMaxLength(300)
                .IsUnicode(false);
            entity.Property(e => e.Status)
                .HasMaxLength(50)
                .IsUnicode(false);

            entity.HasOne(d => d.Order)
                .WithMany(p => p.Shippings)
                .HasForeignKey(d => d.OrderId)
                .OnDelete(DeleteBehavior.ClientSetNull)
                .HasConstraintName("FK_Shippings_Orders");
        });

        OnModelCreatingPartial(modelBuilder);
    }

    partial void OnModelCreatingPartial(ModelBuilder modelBuilder);
}

Example entity created in Entities directory

public partial class Order 
{
    public int OrderId { get; set; }
    [other properties...]
    
    [example relationship]
    public virtual ICollection<Shipping> Shippings { get; set; } = new List<Shippings>();
} 

The partial Class and OnModelCreatingPartial

The generated class is marked partial. This is intentional - it lets you extend the context in a separate file without touching the generated code. If you re-scaffold after a schema change, your customizations survive because they live in a different file.

OnModelCreatingPartial is a partial method hook at the end of OnModelCreating. Add your own configuration in a second partial file by implementing this method there. Never add custom config directly to the generated file.

The Two Constructors

The parameterless constructor exists for design-time tooling. The second constructor - (DbContextOptions<ECommerceDbContext> options) - is the one used at runtime when the context is registered via DI. You do not need to touch either of these.

virtual DbSet Properties

All DbSet properties are marked virtual. This enables mocking in unit tests - a test double can override these properties and return in-memory data without hitting a real database.

OnConfiguring

This is the first thing to fix. The scaffolder drops the connection string directly into OnConfiguring and adds a compiler warning telling you to remove it. Delete the entire method - it is only needed when no options are provided via the constructor, which never happens in a properly configured DI setup.

OnModelCreating - The Real Value

This is where scaffolding earns its keep. Every relationship, constraint, column type, max length, and delete behavior is mapped here using Fluent API. Things to note:

• HasName("PK__...") captures the actual constraint name from SQL Server - useful if you need to reference it later

• HasColumnType("decimal(18,2)") and HasColumnType("datetime") preserve exact SQL Server types that have no direct CLR equivalent

• IsUnicode(false) maps to VARCHAR columns - if you see this, the database is using non-Unicode string columns

• OnDelete(DeleteBehavior.ClientSetNull) reflects the FK delete rule defined in the database - this is not EF’s default, it was read directly from the schema

• HasConstraintName preserves the original FK constraint name from SQL Server

partial void OnModelCreatingPartial

This is the extension point. In a second file, implement it like this to add custom configuration without modifying generated code:

public partial class ECommerceDbContext
{
    partial void OnModelCreatingPartial(ModelBuilder modelBuilder)
    {
        // your custom Fluent API configuration here
        modelBuilder.Entity<Product>()
            .HasQueryFilter(p => !p.IsDeleted);
    }
}

---

Cleaning Up After Scaffolding

Two things to do immediately after the scaffold command runs.

Move the DbContext Up One Level

The DbContext gets placed inside the Entities output folder alongside all the entity files. Move it one level up to the project root or a dedicated Data folder, and update its namespace accordingly.

Remove OnConfiguring and Move the Connection String

Delete the entire OnConfiguring method. Move the connection string to appsettings.json:

Then register the context in Program.cs:

builder.Services.AddDbContext<ECommerceDbContext>(options =>
    options.UseSqlServer(
        builder.Configuration.GetConnectionString("DbConnectionString")));

---

Querying with the Scaffolded Context

With the context registered, a single endpoint demonstrates the full navigation chain across all the generated relationships:

csharp

app.MapGet("/api/demo", async (ECommerceDbContext dbContext) =>
{
    var results = await dbContext.Orders
        .Include(o => o.Customer)
        .Include(o => o.OrderDetails)
            .ThenInclude(od => od.Product)
                .ThenInclude(p => p.Category)
        .Include(o => o.Shippings)
        .Select(o => new
        {
            CustomerName = $"{o.Customer.FirstName} {o.Customer.LastName}",
            o.OrderDate,
            o.TotalAmount,
            o.Status,
            Products = o.OrderDetails.Select(od => new
            {
                od.Product.ProductName,
                od.Quantity,
                od.UnitPrice,
                Categories = od.Product.Category.Name
            }).ToList()
        })
        .ToListAsync();

    return results;
});

• Include / ThenInclude follows the navigation properties generated by scaffolding - no manual wiring needed

• Select projects the result to avoid over-fetching - never return raw EF entities from an API endpoint

• ToListAsync keeps the call non-blocking

---

Pros and Cons of Database First Scaffolding

Pros

• Instant model generation from any existing schema - no manual entity writing

• All Fluent API configuration is generated automatically, including complex relationships

• The partial class pattern lets you extend safely without touching generated code

• Reduces human error when dealing with large or complex schemas

Cons

• Generated code is verbose and can be hard to read in large schemas

• Re-scaffolding on schema changes can overwrite customizations not protected by the partial pattern

• The OnConfiguring connection string issue requires a cleanup step every time

• Navigation property and constraint names are sometimes awkward and may need renaming

When to Use Database First

Use Database First when the database already exists and is maintained independently - legacy systems, DBAs who own the schema, shared databases across multiple services. If you control the database lifecycle from day one, Code First with migrations is the better fit.

---

Key Takeaways

• Three NuGet packages are required before scaffolding: Tools, Design, and the database provider.

• The scaffold command generates both the DbContext and all entities with full Fluent API configuration in a single step.

• Always delete the OnConfiguring method from the generated context and move the connection string to appsettings.json.

• virtual DbSet properties exist for testability - they allow mocking the context in unit tests.

• OnDelete(DeleteBehavior.ClientSetNull), IsUnicode(false), and exact column types like decimal(18,2) are read directly from the database schema - do not change them without understanding the underlying constraint.

Connect with me:

Want to sponsor this newsletter? Let’s work together →