EF Core Database First - Scaffold a DB Context from an Existing Database | Remigiusz Zalewski

ef-core-database-first-scaffold-dbcontext

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 8 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 8 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);
}

The `partial` Class and `OnModelCreatingPartial`

The generated class is marked partial. This 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 by implementing this method in a second partial file. Never add custom config directly to the generated file.

public partial class ECommerceDbContext
{
    partial void OnModelCreatingPartial(ModelBuilder modelBuilder)
    {
        modelBuilder.Entity<Product>()
            .HasQueryFilter(p => !p.IsDeleted);
    }
}

The Two Constructors

The parameterless constructor exists for design-time tooling. The constructor taking DbContextOptions<ECommerceDbContext> is the one used at runtime via DI. You do not need to touch either of these.

`virtual` DbSet Properties

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

`OnConfiguring` - Delete This

This is the first thing to fix after scaffolding. Delete the entire method and move the connection string to appsettings.json. 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

Every relationship, constraint, column type, max length, and delete behavior is mapped here. Things to pay attention to:

HasName("PK__...") captures the actual constraint name from SQL Server
HasColumnType("decimal(18,2)") and HasColumnType("datetime") preserve exact SQL Server types
IsUnicode(false) maps to VARCHAR columns - the database is using non-Unicode strings
OnDelete(DeleteBehavior.ClientSetNull) reflects the FK delete rule read directly from the schema
HasConstraintName preserves the original FK constraint name from SQL Server

Cleaning Up After Scaffolding

Move the DbContext Up One Level

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

Remove `OnConfiguring` and Move the Connection String

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

{
  "ConnectionStrings": {
    "DbConnectionString": "Server=localhost,1433;Database=ECommerceDB;User Id=sa;Password=YourPassword1!;TrustServerCertificate=True"
  }
}

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

⚡ Querying with the Scaffolded Context

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,
                Category = 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 OnConfiguring from the generated context and move the connection string to appsettings.json.
The partial class and OnModelCreatingPartial hook are your extension points - put all custom configuration there, never in the generated file.
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.