Entity Framework Core Database-First Tutorial

Database-first means the database already exists — years of schema, constraints, and data — and EF Core generates the C# model from it. One command reads the schema and writes your entity classes and DbContext. This tutorial scaffolds a model from SQL Server, explains what the generator produces, and covers the two questions every database-first team hits: how to re-scaffold when the schema changes, and how to keep your own code from being overwritten.

The reverse workflow — writing classes first and generating the schema — is covered in EF Core code-first migrations.

Setup

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

The examples use the AdventureWorks sample database — scripts in SQLSampleDatabase if you want to follow along.

Scaffold the model

dotnet ef dbcontext scaffold \
  "Server=localhost;Database=AdventureWorks2022;Trusted_Connection=True;TrustServerCertificate=True" \
  Microsoft.EntityFrameworkCore.SqlServer \
  --output-dir Models \
  --context AdventureWorksContext

Useful flags, all verified in the Web API + EF walkthrough built on this exact command:

• --table Production.Product --table Sales.Customer — scaffold only the tables you need; foreign keys to excluded tables are skipped with a warning.
• --schema Production — everything in one schema.
• --use-database-names — keep exact column casing instead of C#-style names.
• --no-onconfiguring — don't bake the connection string into the context (below).

In Visual Studio's Package Manager Console the equivalent is Scaffold-DbContext with the same options.

What gets generated

For each table: an entity class with a property per column, and in the context a full Fluent API mapping:

public partial class Product
{
    public int ProductId { get; set; }
    public string Name { get; set; } = null!;
    public string ProductNumber { get; set; } = null!;
    public decimal ListPrice { get; set; }
    // …one property per column, navigation properties per FK
}

public partial class AdventureWorksContext : DbContext
{
    public virtual DbSet<Product> Products { get; set; }

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        modelBuilder.Entity<Product>(entity =>
        {
            entity.HasKey(e => e.ProductId).HasName("PK_Product_ProductID");
            entity.ToTable("Product", "Production");
            entity.Property(e => e.Name).HasMaxLength(50);
            // …exact mapping for every column, index, and relationship
        });
    }
}

Everything the database knows — schemas, max lengths, defaults, indexes, relationships — lands in OnModelCreating as Fluent API configuration, so queries behave exactly like the schema demands.

Move the connection string out of the code

The scaffolder embeds your connection string in OnConfiguring and warns about it. Delete that method (or scaffold with --no-onconfiguring) and register the context through DI instead:

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

with the string in appsettings.json (dev) or environment variables/user secrets (everywhere else).

When the database changes

Database-first has no migrations — the database is the source of truth, so you re-scaffold:

dotnet ef dbcontext scaffold "<connection>" Microsoft.EntityFrameworkCore.SqlServer \
  --output-dir Models --context AdventureWorksContext --force

--force overwrites the generated files. That's also the rule that shapes how you extend the model:

• Never edit generated files. Your changes die on the next re-scaffold.
• The generated classes are partial — put your additions in separate files:

// Models/Product.Extensions.cs — survives re-scaffolding
public partial class Product
{
    public decimal DiscountedPrice => ListPrice * 0.9m;
}

• Custom mapping goes in the partial OnModelCreatingPartial hook the generator leaves for you, not in OnModelCreating.

Views, stored procedures, and keyless entities

Scaffolding covers tables; the rest of a mature database maps with a little code.

Views scaffold like tables (they arrive keyless if the view has no key EF can infer). Map one manually as a keyless entity — queryable, never tracked or saved:

public class ProductSummary
{
    public string Name { get; set; } = null!;
    public decimal ListPrice { get; set; }
}

modelBuilder.Entity<ProductSummary>(e =>
{
    e.HasNoKey();
    e.ToView("vProductSummary", "Production");
});

Stored procedures run through FromSql when they return an entity shape, or ExecuteSql for commands:

var products = await context.Products
    .FromSql($"EXEC Production.GetProductsByCategory {categoryId}")
    .ToListAsync();

The composability caveat: you can't stack Where/Include on top of a stored-proc query — SQL Server can't wrap an EXEC in a subquery. Filter inside the procedure or materialize first.

Keeping the connection string safe during scaffolding

The scaffold command needs a live connection, but that doesn't mean secrets on the command line. dotnet ef reads user secrets when pointed at a startup project:

dotnet user-secrets set ConnectionStrings:Default "Server=…;Password=…"
dotnet ef dbcontext scaffold "Name=ConnectionStrings:Default"   Microsoft.EntityFrameworkCore.SqlServer --output-dir Models

The Name= syntax stores only the configuration key in your shell history and scripts — the actual string stays in the secrets store (or environment variable) on each machine.

Database-first or code-first?

• Existing database, DBA-owned schema, multiple apps sharing it → database-first. The schema is the contract; scaffolding keeps you honest.
• New application, schema owned by the code → code-first with migrations; the model evolves in pull requests.
• Mixed reality (existing db, but your app drives changes) → scaffold once to bootstrap, then switch to migrations from that baseline.

Either way, querying is identical — context.Products.Where(...) doesn't care who was first.