Database-First with EF Core: Generating a Model from an Existing Database, Scaffold Options, T4 Templates and Re-Scaffolding

Version note: The code in this post was written and tested with EF Core 10 and SQL Server 2025.

Guess First

So far we have always worked code-first: we wrote the C# classes first and let migrations build the tables. In real life it is often the other way round. The database was there before you, another team owns it, and all you are expected to do is read from it. In Bookstore, the online bookstore used as the shared example across this site's series, the reporting tables are owned by the DBA team. One of them looks like this:

CREATE TABLE reporting.BOOK_SALES (
    SALE_ID     bigint IDENTITY(1,1) NOT NULL CONSTRAINT PK_BOOK_SALES PRIMARY KEY,
    ISBN        char(13) NOT NULL,
    BOOK_TITLE  nvarchar(200) NOT NULL,
    CATEGORY_ID int NOT NULL CONSTRAINT FK_BOOK_SALES_CATEGORY REFERENCES reporting.CATEGORY (CATEGORY_ID),
    SOLD_AT     datetime2(0) NOT NULL CONSTRAINT DF_BOOK_SALES_SOLD_AT DEFAULT (SYSUTCDATETIME()),
    QUANTITY    int NOT NULL CONSTRAINT CK_BOOK_SALES_QUANTITY CHECK (QUANTITY > 0),
    UNIT_PRICE  decimal(10,2) NOT NULL,
    LINE_TOTAL  AS (QUANTITY * UNIT_PRICE) PERSISTED,
    ROW_VER     rowversion NOT NULL
);

You are going to have EF Core generate an entity from this table. What will the class be called? What type of property does LINE_TOTAL become? And if you pass --use-database-names, the option meant to "leave the names alone", does the class keep the name BOOK_SALES? The answers are below; the last one surprises most people.

The DBA's Schema: Bookstore Reporting Tables

The sample schema lives in db/reporting-schema.sql. The reporting schema has a category table, a sales table, an import log table without a primary key, and a view that aggregates daily sales. In dbo there is also an ETL table we don't care about:

CREATE TABLE reporting.IMPORT_LOG (
    FILE_NAME   nvarchar(260) NOT NULL,
    IMPORTED_AT datetime2(0) NOT NULL,
    ROW_COUNT   int NOT NULL
);

CREATE VIEW reporting.vw_DAILY_SALES
AS
SELECT CAST(s.SOLD_AT AS date) AS SALE_DATE,
       c.CATEGORY_NAME,
       SUM(s.QUANTITY)   AS UNITS,
       SUM(s.LINE_TOTAL) AS REVENUE
FROM reporting.BOOK_SALES AS s
JOIN reporting.CATEGORY AS c ON c.CATEGORY_ID = s.CATEGORY_ID
GROUP BY CAST(s.SOLD_AT AS date), c.CATEGORY_NAME;

We won't touch this schema with migrations. The DBA team owns it; our job is to generate a model that reads these tables and to keep that model current as the schema changes. That is what database-first means: the database is the source, and the model is derived from it.

The First Scaffold: Command and Connection String

The solution has two projects: the Bookstore.Reporting class library that will hold the generated code, and Bookstore.Api, which uses it. Scaffolding needs the same pieces as the migration setup in posts 02 and 07: the dotnet-ef local tool (10.0.15), Microsoft.EntityFrameworkCore.Design in the startup project, and the provider package Microsoft.EntityFrameworkCore.SqlServer in the project that receives the code.

The first argument of the command is the connection string. If you type it in directly it works, but EF Core embeds it as-is in the generated OnConfiguring and puts a #warning on top:

protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
#warning To protect potentially sensitive information in your connection string, you should move it out of source code. You can avoid scaffolding the connection string by using the Name= syntax to read it from configuration - see https://go.microsoft.com/fwlink/?linkid=2131148. For more guidance on storing connection strings, see https://go.microsoft.com/fwlink/?LinkId=723263.
    => optionsBuilder.UseSqlServer("Server=localhost,14333;Database=BookstoreDb;User Id=sa;Password=<redacted>;TrustServerCertificate=True");

I removed the password; in the file it was there in plain text. Commit that file and the password goes into the repo history. The right way is the Name= syntax: the tool reads the connection string from the startup project's configuration. The value can come from an environment variable, user-secrets or appsettings.Development.json. With PowerShell on Windows:

$env:ConnectionStrings__Reporting = "Server=localhost;Database=BookstoreDb;Trusted_Connection=True;TrustServerCertificate=True"

dotnet ef dbcontext scaffold Name=ConnectionStrings:Reporting Microsoft.EntityFrameworkCore.SqlServer --project src/Bookstore.Reporting --startup-project src/Bookstore.Api --schema reporting --context ReportingContext --context-dir . --output-dir Entities --namespace Bookstore.Reporting.Entities --context-namespace Bookstore.Reporting --no-onconfiguring

Build started...
Build succeeded.

That's the whole output. The tool built the startup project, took the connection string from configuration, read the tables and the view in the reporting schema and wrote these files:

src/Bookstore.Reporting/ReportingContext.cs
src/Bookstore.Reporting/Entities/BookSale.cs
src/Bookstore.Reporting/Entities/Category.cs
src/Bookstore.Reporting/Entities/ImportLog.cs
src/Bookstore.Reporting/Entities/VwDailySale.cs

Because I passed --no-onconfiguring, the context has no OnConfiguring; the application supplies the connection string through AddDbContext, as in post 16. Without that option, since I used Name=, the embedded value would not have been the password but UseSqlServer("Name=ConnectionStrings:Reporting"), which only works inside a DI container.

The Generated Code

The answers to the opening questions are in BookSale.cs:

public partial class BookSale
{
    public long SaleId { get; set; }

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

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

    public int CategoryId { get; set; }

    public DateTime SoldAt { get; set; }

    public int Quantity { get; set; }

    public decimal UnitPrice { get; set; }

    public decimal? LineTotal { get; set; }

    public byte[] RowVer { get; set; } = null!;

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

The table name was singularized (BOOK_SALES → BookSale) and the upper-case names with underscores became PascalCase. LINE_TOTAL is a computed column, and SQL Server reports it as nullable, so it came out as decimal?. Since the project has NRT enabled, NOT NULL strings came with = null! and the foreign key came with a required navigation. The mapping details are in the context, as Fluent API:

modelBuilder.Entity<BookSale>(entity =>
{
    entity.HasKey(e => e.SaleId);

    entity.ToTable("BOOK_SALES", "reporting");

    entity.HasIndex(e => e.SoldAt, "IX_BOOK_SALES_SOLD_AT");

    entity.Property(e => e.Isbn)
        .HasMaxLength(13)
        .IsUnicode(false)
        .IsFixedLength()
        .HasColumnName("ISBN");
    entity.Property(e => e.LineTotal)
        .HasComputedColumnSql("([QUANTITY]*[UNIT_PRICE])", true)
        .HasColumnType("decimal(21, 2)")
        .HasColumnName("LINE_TOTAL");
    entity.Property(e => e.RowVer)
        .IsRowVersion()
        .IsConcurrencyToken()
        .HasColumnName("ROW_VER");
    entity.Property(e => e.SoldAt)
        .HasPrecision(0)
        .HasDefaultValueSql("(sysutcdatetime())", "DF_BOOK_SALES_SOLD_AT")
        .HasColumnName("SOLD_AT");
    // ... other columns

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

char(13) turned into three settings: length, non-Unicode, fixed length. The rowversion column was recognized as the concurrency token from post 14, and even the name of the default constraint was preserved. The view and the table without a primary key became keyless entities:

modelBuilder.Entity<ImportLog>(entity =>
{
    entity
        .HasNoKey()
        .ToTable("IMPORT_LOG", "reporting");
    // ...
});

modelBuilder.Entity<VwDailySale>(entity =>
{
    entity
        .HasNoKey()
        .ToView("vw_DAILY_SALES", "reporting");
    // ...
});

We met keyless entities in post 15: you can query them, but they aren't tracked and can't be written with SaveChanges. The view's CAST(SOLD_AT AS date) came out as DateOnly?; SQL Server reports expression columns as nullable, so almost everything in a view entity ends up nullable.

I registered the context in the application and wrote two endpoints:

builder.Services.AddDbContext<ReportingContext>(options =>
    options
        .UseSqlServer(builder.Configuration.GetConnectionString("Reporting"))
        .UseQueryTrackingBehavior(QueryTrackingBehavior.NoTracking));

app.MapGet("/reports/daily-sales", async (ReportingContext db) =>
    await db.VwDailySales
        .OrderBy(s => s.SaleDate)
        .ThenBy(s => s.CategoryName)
        .ToListAsync());

app.MapGet("/reports/top-books", async (ReportingContext db) =>
    await db.BookSales
        .GroupBy(s => s.BookTitle)
        .Select(g => new { Title = g.Key, Units = g.Sum(s => s.Quantity) })
        .OrderByDescending(x => x.Units)
        .Take(3)
        .ToListAsync());

GET /reports/top-books
[{"title":"Dune","units":3},{"title":"The Hobbit","units":3},{"title":"Emma","units":1}]

The logged SQL carries the DBA's names, not the C# ones: SELECT TOP(@p) [b].[BOOK_TITLE] AS [Title], COALESCE(SUM([b].[QUANTITY]), 0) AS [Units] FROM [reporting].[BOOK_SALES] AS [b] GROUP BY [b].[BOOK_TITLE]. The reporting model is only ever read, so I turned tracking off at the context level.

Options: Which Tables, Which Names, Where

Let's go through the options in the command one by one. I tried all of them against the same schema.

--schema reporting takes only the tables and views in that schema; that's why dbo.ETL_STATE isn't in the model. To pick tables individually you write --table reporting.BOOK_SALES --table reporting.vw_DAILY_SALES. In that case the other end of the relationship isn't selected, so the tool warns and skips the foreign key:

Skipping foreign key 'FK_BOOK_SALES_CATEGORY' on table 'reporting.BOOK_SALES' since principal table 'reporting.CATEGORY' was not found in the model. This usually happens when the principal table was not included in the selection set.

--use-database-names leaves column and table names as they are. But the answer to the opening question is no: the table name is still singularized.

public partial class BOOK_SALE
{
    public long SALE_ID { get; set; }

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

    public string BOOK_TITLE { get; set; } = null!;
    // ...
}

Turning singularization off is a separate option: --no-pluralize. With it, the class became BookSales and the DbSets became Category and ImportLog. So if you want the names to stay exactly as in the database, you have to pass both options.

--data-annotations moves the mapping into attributes wherever possible: [Table("BOOK_SALES", Schema = "reporting")], [Column("UNIT_PRICE", TypeName = "decimal(10, 2)")], [StringLength(13)], [Unicode(false)]. What attributes can't express (IsFixedLength, the computed column, the default value, rowversion) still stays in OnModelCreating. The discussion from post 04 applies here too; I prefer the Fluent API because it keeps the entity classes clean.

--context-dir and --output-dir decide where the files go, --namespace and --context-namespace decide the namespaces. --force allows overwriting existing files. Without it, the second run stops:

The following file(s) already exist in directory '.../src/Bookstore.Reporting/Entities': ../ReportingContext.cs,BookSale.cs,Category.cs,ImportLog.cs,VwDailySale.cs. Use the Force flag to overwrite these files.

In Visual Studio's Package Manager Console the same command is Scaffold-DbContext; the options are named -Schemas, -Tables, -UseDatabaseNames, -NoPluralize, -DataAnnotations, -NoOnConfiguring and -Force.

What Scaffolding Can't See

Scaffolding turns what the database can tell about itself into a model. What the database can't tell, it can't turn into anything. Our schema has two examples: the CK_BOOK_SALES_QUANTITY check constraint is not in the model, and the INCLUDE (QUANTITY, UNIT_PRICE) part of the index didn't make it into the generated HasIndex line. Neither matters for a read model, but if you try to generate a schema from that same model you get an incomplete schema.

The documentation lists bigger limits as well. Inheritance hierarchies (TPH and TPT from post 19), owned types (post 20) and table splitting leave no trace in the schema; guessing from a discriminator column that a table is TPH is not EF Core's job. These are never scaffolded; if you need them, you set them up by hand afterwards. On the concurrency side, only special types such as SQL Server's rowversion are recognized; if you turned a normal column into a concurrency token as in post 14, scaffolding can't know that. Column types the provider doesn't support never make it into the model either.

Once, or on Every Change?

After scaffolding there are two paths. The first is to scaffold once and take ownership of the code: the generated code becomes your code, and from then on either migrations manage the schema or you update the model by hand as the database changes. That path makes sense if you own the schema and are moving an old database to code-first.

Ours is the second case: another team owns the schema, and when the DBA adds a new column we will scaffold the model again. Then editing the generated files by hand is off-limits, because --force rewrites all of them. EF Core leaves two hooks for customization: everything is generated as partial, and the context ends with a call to an empty OnModelCreatingPartial. I use both in separate files:

// ReportingContext.Custom.cs
public partial class ReportingContext
{
    partial void OnModelCreatingPartial(ModelBuilder modelBuilder)
    {
        // DBA team stores SOLD_AT in UTC; mark the values as UTC when reading.
        modelBuilder.Entity<BookSale>()
            .Property(e => e.SoldAt)
            .HasConversion(v => v, v => DateTime.SpecifyKind(v, DateTimeKind.Utc));
    }

    public override int SaveChanges(bool acceptAllChangesOnSuccess)
        => throw new InvalidOperationException("ReportingContext is read-only.");

    public override Task<int> SaveChangesAsync(bool acceptAllChangesOnSuccess, CancellationToken cancellationToken = default)
        => throw new InvalidOperationException("ReportingContext is read-only.");
}

// Entities/BookSale.Custom.cs
public partial class BookSale
{
    public bool IsBulkSale => Quantity >= 20;
}

OnModelCreatingPartial runs after the generated configuration. In the Fluent API the last call wins, so here you can override a setting made by the scaffolder without deleting it. A property like IsBulkSale has no column and no setter, so EF Core leaves it out of the model. Then I ran the command again with --force: the .Custom.cs files stayed in place and the other five were rewritten from scratch.

Changing the Generated Code with T4 Templates

Partial classes let you add things, but they can't change the shape of the generated code. In our read model I want the properties to use init instead of set, and the navigations not to be virtual; we don't use lazy loading, and a report row shouldn't change after it has been read. For that, EF Core has had T4 templates since EF Core 7:

dotnet new install Microsoft.EntityFrameworkCore.Templates::10.0.15
cd src/Bookstore.Reporting
dotnet new ef-templates

The command adds CodeTemplates/EFCore/DbContext.t4 and EntityType.t4 to the project. When the scaffolder sees this folder, it uses these instead of its built-in templates. The template package version should match the EF Core version; while I was installing it the tool also reminded me that 11.0.0 was out, but in an EF Core 10 project I stayed on 10.0.15. These are the lines I changed in EntityType.t4:

    var source = EntityType.GetViewName() != null
        ? EntityType.GetViewSchema() + "." + EntityType.GetViewName()
        : EntityType.GetSchema() + "." + EntityType.GetTableName();
#>
/// <summary>
/// Read model for <#= source #> (owned by the DBA team). Generated code: put changes in a partial class.
/// </summary>
public partial class <#= EntityType.Name #>

    public <#= code.Reference(property.ClrType) #><#= needsNullable ? "?" : "" #> <#= property.Name #> { get; init; }<#= needsInitializer ? " = null!;" : "" #>

    public ICollection<<#= targetType #>> <#= navigation.Name #> { get; init; } = new List<<#= targetType #>>();

    public <#= targetType #><#= needsNullable ? "?" : "" #> <#= navigation.Name #> { get; init; }<#= needsInitializer ? " = null!;" : "" #>

BookSale.cs after scaffolding again with --force:

/// <summary>
/// Read model for reporting.BOOK_SALES (owned by the DBA team). Generated code: put changes in a partial class.
/// </summary>
public partial class BookSale
{
    public long SaleId { get; init; }

    public string Isbn { get; init; } = null!;
    // ...
    public decimal? LineTotal { get; init; }

    public byte[] RowVer { get; init; } = null!;

    public Category Category { get; init; } = null!;
}

EF Core populates properties with init setters without any trouble; the endpoints above ran on this model. Because the template has the IEntityType in hand, you can reach everything in the model: table name, column comment, key information. Put the CodeTemplates folder in the repo so everyone on the team gets the same output. For a large model, a third template, EntityTypeConfiguration.t4, writes each entity's configuration into its own IEntityTypeConfiguration class as in post 04; ef-templates doesn't add that file by default, you create it yourself.

If you prefer a UI to the command line, ErikEJ's EF Core Power Tools extension does the same job inside Visual Studio: it stores the table selection, naming rules and options in a file and reduces re-scaffolding to a single click. Underneath, the same EF Core scaffolding infrastructure runs.

Common Pitfalls

First, fixing a generated file by hand. If you added a property or an attribute to BookSale.cs, the --force scaffold that follows the DBA's next column silently deletes it. If you miss it in the diff, it's gone. The rule is simple: don't touch generated files; use a partial class, OnModelCreatingPartial or a template.

Second, leaving the connection string in code. If you passed a connection string with a password to the scaffold command, don't ignore the #warning. Use Name=, or don't generate OnConfiguring at all with --no-onconfiguring.

Third, applying migrations to a database-first schema. I tried it: I generated migrations add Initial from the reporting model and ran database update, and EF Core tried to create the tables from scratch.

Microsoft.Data.SqlClient.SqlException (0x80131904): There is already an object named 'CATEGORY' in the database.

Worse, before the error it had already left an __EFMigrationsHistory table in the DBA's database. If you don't own the schema, you don't own the migrations either. If the read model and a model whose schema you manage live in the same project, keep the contexts separate and always pass --context explicitly to migration commands. We'll come back to managing migrations with more than one context in post 32.

Fourth, assuming --use-database-names preserves everything. We saw it above: the table name is still singularized, and you also need --no-pluralize.

A Small Experiment

★☆☆ Scaffold a schema with a few tables from your own database using the Name= syntax and --no-onconfiguring. Then run the same command with --use-database-names, and then with --use-database-names --no-pluralize, and compare the class names in the three outputs.

★★☆ Add a new column to the table (ALTER TABLE reporting.BOOK_SALES ADD CHANNEL varchar(20) NULL). Scaffold again with --force and confirm that the .Custom.cs files are still there and the new property has arrived.

★★★ Change EntityType.t4: check whether it writes the column comment (the MS_Description added with sp_addextendedproperty) as an XML summary above the property, and add it if not. Then have it generate a separate "Keyless: read only" line above the class for keyless entities.

In this post we generated the model from the database and wrote the queries in LINQ. But which part of your LINQ turns into which SQL function? How do ToLower, AddDays, Math.Round or a SQL function you wrote yourself get translated? In the next post we'll put together EF Core's LINQ-to-SQL translation catalog on SQL Server, along with EF.Functions and HasDbFunction.