Database-First 逆向工程

概述

Database-First(数据库优先)是一种从现有数据库生成 EF Core 模型的开发模式。在这种模式下,开发人员首先设计并创建数据库,然后使用 EF Core 提供的脚手架(Scaffolding)工具自动生成 C# 实体类和 DbContext。

适用场景

✅ 遗留系统集成: 已有成熟的数据库系统,需要在其基础上构建 .NET 应用
✅ DBA主导项目: 数据库由专业 DBA 团队设计和维护
✅ 复杂数据库结构: 包含大量存储过程、触发器、视图等数据库对象
✅ 跨平台迁移: 从其他技术栈迁移到 .NET,保留原有数据库
❌ 新项目开发: 推荐使用 Code-First,更灵活可控
❌ 频繁变更: 每次数据库变更都需要重新生成代码

技术优势

优势	说明
快速启动	几分钟内生成完整的数据访问层
准确性	自动映射数据库类型和约束
可视化	利用 SSMS/Navicat 等工具管理数据库
兼容性	支持 SQL Server、PostgreSQL、MySQL 等主流数据库
增量更新	支持基于变更重新生成部分代码

环境准备

安装必备工具

bash

# 全局安装 EF Core CLI 工具
dotnet tool install --global dotnet-ef

# 验证安装
dotnet ef --version
# 输出: Entity Framework Core .NET Command-line Tools 8.0.x

# 在项目级别安装(推荐团队协作)
dotnet new console -n MyProject
cd MyProject
dotnet tool install dotnet-ef

添加 NuGet 包

xml

<Project Sdk="Microsoft.NET.Sdk">

  <PropertyGroup>
    <TargetFramework>net8.0</TargetFramework>
    <ImplicitUsings>enable</ImplicitUsings>
    <Nullable>enable</Nullable>
  </PropertyGroup>

  <ItemGroup>
    <!-- SQL Server 提供程序 -->
    <PackageReference Include="Microsoft.EntityFrameworkCore.SqlServer" Version="8.0.0" />
    
    <!-- PostgreSQL 提供程序(可选) -->
    <!-- <PackageReference Include="Npgsql.EntityFrameworkCore.PostgreSQL" Version="8.0.0" /> -->
    
    <!-- MySQL 提供程序(可选) -->
    <!-- <PackageReference Include="Pomelo.EntityFrameworkCore.MySql" Version="8.0.0" /> -->
    
    <!-- 设计时支持(必需) -->
    <PackageReference Include="Microsoft.EntityFrameworkCore.Design" Version="8.0.0">
      <PrivateAssets>all</PrivateAssets>
      <IncludeAssets>runtime; build; native; contentfiles; analyzers; buildtransitive</IncludeAssets>
    </PackageReference>
  </ItemGroup>

</Project>

基础逆向工程

连接字符串配置

csharp

// appsettings.json
{
  "ConnectionStrings": {
    // SQL Server
    "DefaultConnection": "Server=localhost;Database=AdventureWorks;Trusted_Connection=True;TrustServerCertificate=True;",
    
    // PostgreSQL
    // "DefaultConnection": "Host=localhost;Port=5432;Database=adventureworks;Username=postgres;Password=your_password",
    
    // MySQL
    // "DefaultConnection": "Server=localhost;Port=3306;Database=adventureworks;Uid=root;Pwd=your_password"
  }
}

基本脚手架命令

bash

# 基本语法
dotnet ef dbcontext scaffold "<connection_string>" <provider>

# SQL Server 示例
dotnet ef dbcontext scaffold \
  "Server=localhost;Database=AdventureWorks;Trusted_Connection=True;TrustServerCertificate=True;" \
  Microsoft.EntityFrameworkCore.SqlServer

# PostgreSQL 示例
dotnet ef dbcontext scaffold \
  "Host=localhost;Port=5432;Database=adventureworks;Username=postgres;Password=your_password" \
  Npgsql.EntityFrameworkCore.PostgreSQL

# MySQL 示例
dotnet ef dbcontext scaffold \
  "Server=localhost;Port=3306;Database=adventureworks;Uid=root;Pwd=your_password" \
  Pomelo.EntityFrameworkCore.MySql

常用参数详解

bash

# 完整参数示例
dotnet ef dbcontext scaffold \
  "Server=localhost;Database=AdventureWorks;Trusted_Connection=True;TrustServerCertificate=True;" \
  Microsoft.EntityFrameworkCore.SqlServer \
  --output-dir Models \                    # 输出目录
  --context-dir Data \                     # DbContext 输出目录
  --context AdventureWorksContext \        # DbContext 类名
  --schema Sales,Production \              # 指定架构
  --table "Sales.SalesOrderHeader","Sales.SalesOrderDetail" \  # 指定表
  --data-annotations \                     # 使用数据注解
  --force                                  # 覆盖现有文件

参数	说明	示例
`--output-dir` / `-o`	实体类输出目录	`Models`
`--context-dir`	DbContext 输出目录	`Data`
`--context`	DbContext 类名	`AppDbContext`
`--schema`	指定架构(逗号分隔)	`Sales,Production`
`--table` / `-t`	指定表(可多个)	`dbo.Products`
`--data-annotations`	使用数据注解而非 Fluent API	-
`--force` / `-f`	覆盖现有文件	-
`--no-pluralize`	禁用复数化命名	-
`--use-database-names`	使用数据库中的名称	-
`--namespace`	自定义命名空间	`MyApp.Data`

高级配置选项

按架构筛选

bash

# 仅生成特定架构的表
dotnet ef dbcontext scaffold \
  "Server=localhost;Database=AdventureWorks;Trusted_Connection=True;TrustServerCertificate=True;" \
  Microsoft.EntityFrameworkCore.SqlServer \
  --schema Sales \
  --schema Production \
  --output-dir Models/Sales \
  --context SalesContext

按表筛选

bash

# 仅生成指定的表
dotnet ef dbcontext scaffold \
  "Server=localhost;Database=AdventureWorks;Trusted_Connection=True;TrustServerCertificate=True;" \
  Microsoft.EntityFrameworkCore.SqlServer \
  --table "Sales.Customer" \
  --table "Sales.SalesOrderHeader" \
  --table "Production.Product" \
  --output-dir Models

使用数据注解

bash

# 使用数据注解替代 Fluent API
dotnet ef dbcontext scaffold \
  "Server=localhost;Database=AdventureWorks;Trusted_Connection=True;TrustServerCertificate=True;" \
  Microsoft.EntityFrameworkCore.SqlServer \
  --data-annotations \
  --output-dir Models

生成的实体类将使用属性标记:

csharp

// 使用数据注解生成的实体
using System.ComponentModel.DataAnnotations;
using System.ComponentModel.DataAnnotations.Schema;

[Table("Product", Schema = "Production")]
public partial class Product
{
    [Key]
    [Column("ProductID")]
    public int ProductId { get; set; }
    
    [Required]
    [StringLength(50)]
    public string Name { get; set; } = null!;
    
    [Column(TypeName = "decimal(19,4)")]
    public decimal StandardCost { get; set; }
    
    [Column(TypeName = "decimal(19,4)")]
    public decimal ListPrice { get; set; }
    
    [ForeignKey("ProductSubcategoryID")]
    public int? SubcategoryId { get; set; }
    
    [InverseProperty("Product")]
    public virtual ICollection<SalesOrderDetail> SalesOrderDetails { get; set; } = new List<SalesOrderDetail>();
}

自定义命名空间和类名

bash

# 自定义命名空间和 DbContext 名称
dotnet ef dbcontext scaffold \
  "Server=localhost;Database=AdventureWorks;Trusted_Connection=True;TrustServerCertificate=True;" \
  Microsoft.EntityFrameworkCore.SqlServer \
  --context AppDbContext \
  --namespace MyApp.Infrastructure.Data \
  --context-namespace MyApp.Infrastructure.Data \
  --output-dir Models

生成的代码结构

DbContext 类

csharp

// AdventureWorksContext.cs
using Microsoft.EntityFrameworkCore;

namespace AdventureWorks.Models;

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

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

    // DbSets
    public virtual DbSet<Customer> Customers { get; set; }
    public virtual DbSet<Product> Products { get; set; }
    public virtual DbSet<SalesOrderHeader> SalesOrderHeaders { get; set; }
    public virtual DbSet<SalesOrderDetail> SalesOrderDetails { get; set; }

    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 http://go.microsoft.com/fwlink/?LinkId=723263.
        => optionsBuilder.UseSqlServer("Server=localhost;Database=AdventureWorks;Trusted_Connection=True;TrustServerCertificate=True;");

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        // Fluent API 配置
        modelBuilder.Entity<Customer>(entity =>
        {
            entity.HasKey(e => e.CustomerId).HasName("PK_Customer_CustomerID");
            
            entity.ToTable("Customer", "Sales");
            
            entity.Property(e => e.CustomerId).HasColumnName("CustomerID");
            entity.Property(e => e.AccountNumber)
                .HasMaxLength(10)
                .IsUnicode(false)
                .HasComputedColumnSql("(isnull('AW'+[dbo].[ufnLeadingZeros]([CustomerID]),''))", false);
            entity.Property(e => e.PersonId).HasColumnName("PersonID");
            entity.Property(e => e.StoreId).HasColumnName("StoreID");
            entity.Property(e => e.TerritoryId).HasColumnName("TerritoryID");
            
            entity.HasOne(d => d.Person).WithMany(p => p.Customers)
                .HasForeignKey(d => d.PersonId)
                .HasConstraintName("FK_Customer_Person_PersonID");
        });

        modelBuilder.Entity<Product>(entity =>
        {
            entity.HasKey(e => e.ProductId).HasName("PK_Product_ProductID");
            
            entity.ToTable("Product", "Production");
            
            entity.Property(e => e.ProductId).HasColumnName("ProductID");
            entity.Property(e => e.Color).HasMaxLength(15);
            entity.Property(e => e.DaysToManufacture).HasColumnName("DaysToManufacture");
            entity.Property(e => e.DiscontinuedDate).HasColumnType("datetime");
            entity.Property(e => e.ListPrice).HasColumnType("money");
            entity.Property(e => e.ModifiedDate)
                .HasDefaultValueSql("(getdate())")
                .HasColumnType("datetime");
            entity.Property(e => e.Name)
                .HasMaxLength(50)
                .HasColumnName("Name");
            entity.Property(e => e.ProductLine)
                .HasMaxLength(2)
                .IsUnicode(false)
                .IsFixedLength();
            entity.Property(e => e.ProductNumber)
                .HasMaxLength(25)
                .IsUnicode(false);
            entity.Property(e => e.ReorderPoint).HasColumnName("ReorderPoint");
            entity.Property(e => e.SellEndDate).HasColumnType("datetime");
            entity.Property(e => e.SellStartDate).HasColumnType("datetime");
            entity.Property(e => e.StandardCost).HasColumnType("money");
            entity.Property(e => e.Weight).HasColumnType("decimal(8, 2)");
            
            entity.HasOne(d => d.ProductSubcategory).WithMany(p => p.Products)
                .HasForeignKey(d => d.SubcategoryId)
                .HasConstraintName("FK_Product_ProductSubcategory_ProductSubcategoryID");
        });

        OnModelCreatingPartial(modelBuilder);
    }

    partial void OnModelCreatingPartial(ModelBuilder modelBuilder);
}

实体类示例

csharp

// Customer.cs
using System;
using System.Collections.Generic;

namespace AdventureWorks.Models;

public partial class Customer
{
    public Customer()
    {
        SalesOrderHeaders = new HashSet<SalesOrderHeader>();
    }

    public int CustomerId { get; set; }
    public int? PersonId { get; set; }
    public int? StoreId { get; set; }
    public int? TerritoryId { get; set; }
    public string AccountNumber { get; set; } = null!;
    public Guid Rowguid { get; set; }
    public DateTime ModifiedDate { get; set; }

    // 导航属性
    public virtual Person? Person { get; set; }
    public virtual Store? Store { get; set; }
    public virtual SalesTerritory? Territory { get; set; }
    public virtual ICollection<SalesOrderHeader> SalesOrderHeaders { get; set; }
}

csharp

// SalesOrderHeader.cs
using System;
using System.Collections.Generic;

namespace AdventureWorks.Models;

public partial class SalesOrderHeader
{
    public SalesOrderHeader()
    {
        SalesOrderDetails = new HashSet<SalesOrderDetail>();
    }

    public int SalesOrderId { get; set; }
    public int? RevisionNumber { get; set; }
    public DateTime OrderDate { get; set; }
    public DateTime DueDate { get; set; }
    public DateTime? ShipDate { get; set; }
    public byte Status { get; set; }
    public bool? OnlineOrderFlag { get; set; }
    public string SalesOrderNumber { get; set; } = null!;
    public int? PurchaseOrderNumber { get; set; }
    public string? AccountNumber { get; set; }
    public int CustomerId { get; set; }
    public int? SalesPersonId { get; set; }
    public int? TerritoryId { get; set; }
    public int BillToAddressId { get; set; }
    public int ShipToAddressId { get; set; }
    public int ShipMethodId { get; set; }
    public int? CreditCardId { get; set; }
    public string? CreditCardApprovalCode { get; set; }
    public int? CurrencyRateId { get; set; }
    public decimal SubTotal { get; set; }
    public decimal TaxAmt { get; set; }
    public decimal Freight { get; set; }
    public decimal TotalDue { get; set; }
    public string? Comment { get; set; }
    public Guid Rowguid { get; set; }
    public DateTime ModifiedDate { get; set; }

    // 导航属性
    public virtual Address BillToAddress { get; set; } = null!;
    public virtual CurrencyRate? CurrencyRate { get; set; }
    public virtual Customer Customer { get; set; } = null!;
    public virtual Person? SalesPerson { get; set; }
    public virtual ShipMethod ShipMethod { get; set; } = null!;
    public virtual Address ShipToAddress { get; set; } = null!;
    public virtual SalesTerritory? Territory { get; set; }
    public virtual ICollection<SalesOrderDetail> SalesOrderDetails { get; set; }
}

部分方法扩展机制

EF Core 脚手架使用 partial 方法,允许你在不修改生成代码的情况下进行扩展:

csharp

// CustomDbContextExtensions.cs
using Microsoft.EntityFrameworkCore;

namespace AdventureWorks.Models;

public partial class AdventureWorksContext
{
    // 实现部分方法来自定义模型配置
    partial void OnModelCreatingPartial(ModelBuilder modelBuilder)
    {
        // 添加自定义配置
        
        // 1. 全局查询过滤器(软删除)
        modelBuilder.Entity<Product>()
            .HasQueryFilter(p => !p.DiscontinuedDate.HasValue);
        
        // 2. 自定义索引
        modelBuilder.Entity<Customer>()
            .HasIndex(c => c.AccountNumber)
            .IsUnique();
        
        // 3. 值转换
        modelBuilder.Entity<SalesOrderHeader>()
            .Property(o => o.Status)
            .HasConversion<byte>();
        
        // 4. 种子数据
        modelBuilder.Entity<SalesTerritory>().HasData(
            new SalesTerritory 
            { 
                TerritoryId = 1, 
                Name = "Northwest", 
                CountryRegionCode = "US",
                Group = "North America",
                SalesYtd = 3000000.00m,
                SalesLastYear = 2500000.00m,
                CostYtd = 1500000.00m,
                CostLastYear = 1200000.00m,
                ModifiedDate = DateTime.Now
            }
        );
    }
}

处理复杂数据库对象

视图映射

bash

# 脚手架会检测并生成视图映射
dotnet ef dbcontext scaffold \
  "Server=localhost;Database=AdventureWorks;Trusted_Connection=True;TrustServerCertificate=True;" \
  Microsoft.EntityFrameworkCore.SqlServer \
  --table "Sales.vSalesPerson" \
  --output-dir Models

csharp

// vSalesPerson.cs (视图实体)
using System;
using System.Collections.Generic;

namespace AdventureWorks.Models;

/// <summary>
/// 销售人员视图 - 只读
/// </summary>
public partial class VSalesPerson
{
    public int BusinessEntityId { get; set; }
    public string FirstName { get; set; } = null!;
    public string LastName { get; set; } = null!;
    public string JobTitle { get; set; } = null!;
    public string? PhoneNumber { get; set; }
    public string? EmailAddress { get; set; }
    public string City { get; set; } = null!;
    public string StateProvinceName { get; set; } = null!;
    public string CountryRegionName { get; set; } = null!;
    public string? TerritoryName { get; set; }
    public string? TerritoryGroup { get; set; }
    public decimal? SalesQuota { get; set; }
    public decimal SalesYtd { get; set; }
    public decimal SalesLastYear { get; set; }
    
    // ⚠️ 视图没有主键,需要配置无键实体
}

csharp

// 在 OnModelCreating 中配置
modelBuilder.Entity<VSalesPerson>(entity =>
{
    entity.HasNoKey();  // 视图无主键
    entity.ToView("vSalesPerson", "Sales");  // 映射到视图
});

存储过程调用

csharp

// 通过 FromSqlRaw 调用存储过程
public partial class AdventureWorksContext
{
    public async Task<List<Product>> GetProductsByCategoryAsync(string category)
    {
        return await Products
            .FromSqlRaw("EXEC Production.uspGetProductsByCategory @Category = {0}", category)
            .ToListAsync();
    }
    
    public async Task<int> UpdateProductPriceAsync(int productId, decimal newPrice)
    {
        var resultParam = new SqlParameter("@Result", SqlDbType.Int)
        {
            Direction = ParameterDirection.Output
        };
        
        await Database.ExecuteSqlRawAsync(
            "EXEC Production.uspUpdateProductPrice @ProductId = {0}, @NewPrice = {1}, @Result = {2} OUTPUT",
            productId, newPrice, resultParam);
        
        return (int)resultParam.Value;
    }
}

计算列

csharp

// 脚手架会自动识别计算列
modelBuilder.Entity<Product>(entity =>
{
    // SellableInventory 是计算列
    entity.Property(e => e.SellableInventory)
        .HasComputedColumnSql("([QuantityInStock] - [ReorderPoint])", false);
});

增量更新策略

问题: 数据库变更后的同步

当数据库结构发生变化时,需要重新运行脚手架命令。但直接覆盖会导致自定义代码丢失。

解决方案 1: 使用部分方法

csharp

// ✅ 推荐: 所有自定义逻辑放在部分方法中
public partial class AdventureWorksContext
{
    partial void OnModelCreatingPartial(ModelBuilder modelBuilder)
    {
        // 这里的所有自定义代码都会在重新生成后保留
    }
}

解决方案 2: 继承 DbContext

csharp

// CustomDbContext.cs
public class CustomDbContext : AdventureWorksContext
{
    public CustomDbContext(DbContextOptions<CustomDbContext> options)
        : base(options)
    {
    }

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        base.OnModelCreating(modelBuilder);
        
        // 在这里添加自定义配置
        modelBuilder.Entity<Product>()
            .HasQueryFilter(p => !p.DiscontinuedDate.HasValue);
    }
}

解决方案 3: 版本控制生成的代码

bash

# 1. 首次生成时提交到 Git
git add Models/
git commit -m "Initial EF scaffolding"

# 2. 数据库变更后,重新生成
dotnet ef dbcontext scaffold ... --force

# 3. 查看差异
git diff Models/

# 4. 手动审查并提交
git add Models/
git commit -m "Regenerate models after database changes"

最佳实践

✅ 推荐做法

1. 组织生成的代码

src/
├── Data/
│   ├── Ad

Database-First 逆向工程 ​

概述 ​

适用场景 ​

技术优势 ​

环境准备 ​

安装必备工具 ​

添加 NuGet 包 ​

基础逆向工程 ​

连接字符串配置 ​

基本脚手架命令 ​

常用参数详解 ​

高级配置选项 ​

按架构筛选 ​

按表筛选 ​

使用数据注解 ​

自定义命名空间和类名 ​

生成的代码结构 ​

DbContext 类 ​

实体类示例 ​

部分方法扩展机制 ​

处理复杂数据库对象 ​

视图映射 ​

存储过程调用 ​

计算列 ​

增量更新策略 ​

问题: 数据库变更后的同步 ​

解决方案 1: 使用部分方法 ​

解决方案 2: 继承 DbContext ​

解决方案 3: 版本控制生成的代码 ​

最佳实践 ​

✅ 推荐做法 ​

1. 组织生成的代码 ​

Database-First 逆向工程

概述

适用场景

技术优势

环境准备

安装必备工具

添加 NuGet 包

基础逆向工程

连接字符串配置

基本脚手架命令

常用参数详解

高级配置选项

按架构筛选

按表筛选

使用数据注解

自定义命名空间和类名

生成的代码结构

DbContext 类

实体类示例

部分方法扩展机制

处理复杂数据库对象

视图映射

存储过程调用

计算列

增量更新策略

问题: 数据库变更后的同步

解决方案 1: 使用部分方法

解决方案 2: 继承 DbContext

解决方案 3: 版本控制生成的代码

最佳实践

✅ 推荐做法

1. 组织生成的代码