趣事：
在做项目开发过程中，如何用一套代码兼容多种类型的数据库。
直接开干！
第一章：引言与数据库提供者概述
1.1 为什么需要支持多种数据库？
在现代企业级应用开发中，数据库选型往往受多种因素影响：
- 成本：SQL Server 商业授权费用高，而 PostgreSQL 和 SQLite 是开源免费的。
- 部署环境：嵌入式或本地开发常选用 SQLite；云原生或高并发场景倾向 PostgreSQL 或 SQL Server。
- 团队技能
- 合规与地域限制
- 可移植性需求：希望一套代码能适配多个数据库后端（如 SaaS 多租户架构）。
因此，.NET 开发者必须掌握如何在 Entity Framework Core（EF Core）或其他 ORM/数据访问层中灵活切换数据库提供者。
1.2 .NET 中的数据库提供者（Database Providers）
在 EF Core 生态中，数据库提供者（Database Provider）是一个实现了 Microsoft.EntityFrameworkCore.Storage.IDatabaseProvider 接口的 NuGet 包，它负责将 EF Core 的通用 LINQ 查询和模型操作翻译成特定数据库的 SQL 方言，并处理连接、事务、迁移等底层细节。
主流提供者包括：
| | | | |
|---|
| Microsoft.EntityFrameworkCore.SqlServer | | | 企业级 Windows/.NET 生态、Azure 集成 |
| Npgsql.EntityFrameworkCore.PostgreSQL | | | |
| Microsoft.EntityFrameworkCore.Sqlite | | | |
注：虽然 PostgreSQL 提供者非微软官方直接维护，但 Npgsql 团队与 EF Core 团队紧密合作，兼容性和质量极高，被广泛视为“准官方”。
1.3 EF Core 的抽象设计优势
EF Core 通过以下机制实现数据库无关性：
- 统一的 DbContext API：无论后端是什么，开发者都使用
DbContext、DbSet<T>、SaveChanges() 等。 - LINQ to Entities
- 迁移系统（Migrations）：
dotnet ef migrations add 生成的迁移文件会根据当前配置的提供者生成对应 DDL。 - 值转换器（Value Converters）：处理类型映射差异（如 bool → TINYINT vs BOOLEAN）。
- 原始 SQL 支持：必要时可通过
FromSqlRaw 执行原生 SQL，但需注意可移植性。
1.4 本章小结
本章介绍了多数据库支持的现实意义、.NET 中主流数据库提供者的概况，以及 EF Core 如何通过抽象层实现跨数据库兼容。后续章节将深入每个数据库的特性、配置方式、性能差异、迁移策略，并通过完整项目 Demo 展示实战技巧。
第二章：SQL Server 在 .NET 中的深度集成与优化
2.1 SQL Server 简介与适用场景
Microsoft SQL Server 是微软开发的关系型数据库管理系统（RDBMS），广泛应用于企业级 Windows/.NET 生态系统中。其主要特点包括：
- 强事务一致性：支持 ACID，适用于金融、ERP 等高可靠性场景。
- 与 Azure 深度集成：Azure SQL Database、SQL Managed Instance 提供无缝云迁移路径。
- 丰富的工具链：SQL Server Management Studio (SSMS)、Profiler、Always On 可用性组等。
- 高级功能支持：全文搜索、列存储索引、内存优化表（In-Memory OLTP）、CLR 集成等。
在 .NET 项目中，SQL Server 常用于：
- 需要与 Active Directory 集成的身份认证系统
- 已有大量 SQL Server 投资的遗留系统现代化改造
2.2 在 .NET 中配置 SQL Server 提供者
2.2.1 安装 NuGet 包
dotnet add package Microsoft.EntityFrameworkCore.SqlServer
注意：EF Core 版本需与 .NET SDK 版本兼容。例如，.NET 8 推荐使用 EF
public class AppDbContext : DbContext{ public DbSet<User> Users { get; set; } protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder) { optionsBuilder.UseSqlServer("Server=localhost;Database=MyApp;Trusted_Connection=true;TrustServerCertificate=true;"); }}
builder.Services.AddDbContext<AppDbContext>(options => options.UseSqlServer(builder.Configuration.GetConnectionString("DefaultConnection")));
{ "ConnectionStrings": { "DefaultConnection": "Server=(localdb)\\mssqllocaldb;Database=MyAppDb;Trusted_Connection=true;MultipleActiveResultSets=true" }}
关键连接字符串参数说明：
Trusted_Connection=true：使用 Windows 身份验证（开发环境常用）。MultipleActiveResultSets=true（MARS）：允许单个连接上执行多个活动结果集，避免“已有 DataReader 打开”异常。TrustServerCertificate=true
2.3 SQL Server 特有功能在 EF Core 中的使用
2.3.1 计算列（Computed Columns）
SQL Server 支持在表中定义由表达式计算得出的列。EF Core 可映射此类列为只读属性。
public class Order{ public int Id { get; set; } public decimal Price { get; set; } public decimal TaxRate { get; set; }
[DatabaseGenerated(DatabaseGeneratedOption.Computed)] public decimal Total => Price * (1 + TaxRate);}
protected override void Up(MigrationBuilder migrationBuilder){ migrationBuilder.AddColumn<decimal>( name: "Total", table: "Orders", type: "decimal(18,2)", computedColumnSql: "[Price] * (1 + [TaxRate])");}
2.3.2 序列（Sequences）
SQL Server 支持 SEQUENCE 对象，可用于生成全局唯一 ID（比 IDENTITY 更灵活）。
modelBuilder.HasSequence<int>("OrderNumberSeq") .StartsAt(1000) .IncrementsBy(1);modelBuilder.Entity<Order>() .Property(o => o.OrderNumber) .HasDefaultValueSql("NEXT VALUE FOR OrderNumberSeq");
2.3.3 内存优化表（In-Memory OLTP）
虽然 EF Core 不直接支持内存优化表的 DDL 创建，但可通过原始 SQL 或迁移脚本实现，并在查询时正常访问。
注意：内存表不支持外键、LOB 类型（如 nvarchar(max)），需谨慎设计模型。
2.4 性能调优最佳实践
2.4.1 使用异步方法
始终优先使用 ToListAsync(), SaveChangesAsync() 等异步 API，避免阻塞线程池。
var users = await context.Users.Where(u => u.IsActive).ToListAsync();
2.4.2 避免 N+1 查询
使用 Include 或 Select 显式加载关联数据：
foreach (var order in orders){ Console.WriteLine(order.Customer.Name); }var orders = context.Orders.Include(o => o.Customer).ToList();
optionsBuilder.LogTo(Console.WriteLine, LogLevel.Information);optionsBuilder.EnableSensitiveDataLogging();
2.4.4 使用批处理（EF Core 7+）
EF Core 7 引入了 ExecuteUpdate 和 ExecuteDelete，可直接生成 UPDATE ... WHERE 而无需先加载实体：
await context.Users .Where(u => u.Status == "Inactive") .ExecuteUpdateAsync(setters => setters.SetProperty(u => u.IsArchived, true));
2.5 迁移（Migrations）管理
SQL Server 的迁移生成高度可靠，支持：
- 自动检测模型变更（Add/Remove/Rename 列、表）
- 自定义 SQL 脚本嵌入（
migrationBuilder.Sql("...")）
常见问题：
- LocalDB vs Full SQL Server：LocalDB 不支持某些高级功能（如分区表），迁移可能在开发环境成功但在生产失败。
- 权限问题：部署时确保应用账户有
db_ddladmin 权限（或使用独立迁移用户）。
建议：
- 使用
dotnet ef migrations script 生成 SQL 脚本，交由 DBA 审核后执行。 - 在 CI/CD 中使用
dotnet ef database update 自动应用迁移（需谨慎控制权限）。
2.6 与 Azure SQL 的集成
若部署到 Azure，可利用：
- 连接弹性：通过
EnableRetryOnFailure() 启用自动重试策略：
options.UseSqlServer( connectionString, sqlServerOptions => sqlServerOptions.EnableRetryOnFailure( maxRetryCount: 5, maxRetryDelay: TimeSpan.FromSeconds(30), errorNumbersToAdd: null));
var tokenProvider = new AzureServiceTokenProvider();var connection = new SqlConnection(connectionString);connection.AccessToken = await tokenProvider.GetAccessTokenAsync("https://database.windows.net/");
需配合 Microsoft.Data.SqlClient 2.1+ 和 Azure.Identity。
2.7 本章小结
本章深入探讨了 SQL Server 在 .NET 项目中的配置、特有功能使用、性能优化策略以及与 Azure 云服务的集成。SQL Server 凭借其稳定性、工具生态和微软官方支持，仍是 .NET 企业应用的首选数据库之一。然而，其许可成本和 Windows 依赖性也促使许多团队转向开源替代方案。
第三章：PostgreSQL —— 开源世界的高性能之选
3.1 PostgreSQL 简介与核心优势
PostgreSQL（常简称为 Postgres）是一个功能强大、开源的对象-关系型数据库系统，以其标准兼容性、可扩展性和稳定性著称。自 1996 年发布以来，已成为全球最受欢迎的开源数据库之一。
核心优势：
- 完全开源免费
- 高级数据类型支持：
JSON/JSONB、HStore（键值对）、Array、Range、UUID、INET（IP 地址）等。 - 强大的全文搜索（Full-Text Search）
- 地理空间能力：通过 PostGIS 扩展支持 GIS（地理信息系统），广泛用于地图、物流、LBS 应用。
- 高并发与 MVCC：多版本并发控制（MVCC）确保读写不阻塞，适合高负载场景。
- 可扩展性：支持自定义函数（PL/pgSQL、Python、JavaScript 等）、操作符、索引类型（如 GIN、GiST）。
在 .NET 生态中，PostgreSQL 常用于：
- 成本敏感或云原生架构（如部署在 AWS RDS、Azure Database for PostgreSQL）
3.2 在 .NET 中配置 PostgreSQL 提供者
3.2.1 安装 NuGet 包
dotnet add package Npgsql.EntityFrameworkCore.PostgreSQL
注意：Npgsql 是 .NET 对 PostgreSQL 的官方驱动，由社区维护但质量极高，与 EF Core 团队紧密协作。
builder.Services.AddDbContext<AppDbContext>(options => options.UseNpgsql(builder.Configuration.GetConnectionString("PostgresConnection")));
{ "ConnectionStrings": { "PostgresConnection": "Host=localhost;Port=5432;Database=myapp;Username=postgres;Password=yourpassword" }}
连接字符串关键参数：
HostUsernamePooling=trueCommandTimeout=30Include Error Detail=true
3.3 PostgreSQL 特有功能在 EF Core 中的使用
3.3.1 JSONB 类型映射
PostgreSQL 的 JSONB（二进制 JSON）支持高效查询和索引。EF Core 可直接映射为 C# 的 Dictionary<string, object>、JsonDocument 或自定义 POCO。
示例：用户偏好设置
public class User{ public int Id { get; set; } public string Name { get; set; }
[Column(TypeName = "jsonb")] public Preferences Preferences { get; set; }}public class Preferences{ public string Theme { get; set; } public bool NotificationsEnabled { get; set; } public List<string> FavoriteTags { get; set; }}
查询 JSONB 字段（需使用 EF.Functions.JsonContains 或原始 SQL）：
var users = context.Users .Where(u => EF.Functions.JsonContains(u.Preferences, @"{""NotificationsEnabled"":true}")) .ToList();
注意：EF Core 7+ 开始原生支持 JsonProperty 和更丰富的 JSON 查询，但 PostgreSQL 仍需依赖 EF.Functions 扩展方法。
3.3.2 数组类型（Array）
PostgreSQL 支持原生数组类型（如 text[]、int[]）。
public class Product{ public int Id { get; set; } public string Name { get; set; }
[Column(TypeName = "text[]")] public string[] Tags { get; set; }}
var products = context.Products .Where(p => p.Tags.Contains("electronics")) .ToList();
Npgsql 会自动将其翻译为 WHERE 'electronics' = ANY("Tags")。
3.3.3 全文搜索（Full-Text Search）
PostgreSQL 内置 tsvector 和 tsquery 类型支持全文检索。
虽然 EF Core 不直接暴露这些类型，但可通过原始 SQL 实现：
var keyword = "database";var results = context.BlogPosts .FromSqlRaw(@" SELECT * FROM ""BlogPosts"" WHERE to_tsvector('english', ""Content"") @@ plainto_tsquery('english', {0})", keyword) .ToList();
优化建议：为 tsvector 列创建 GIN 索引以加速查询。
3.3.4 UUID 主键
PostgreSQL 原生支持 UUID 类型，比 SQL Server 的 uniqueidentifier 更高效。
public class Order{ [Key] [DatabaseGenerated(DatabaseGeneratedOption.None)] public Guid Id { get; set; } = Guid.NewGuid();}
modelBuilder.Entity<Order>() .Property(o => o.Id) .HasColumnType("uuid");
优势：避免自增 ID 泄露业务量，支持分布式系统 ID 生成。
3.4 性能调优与最佳实践
3.4.1 启用 Npgsql 日志
optionsBuilder.UseNpgsql(connectionString) .LogTo(Console.WriteLine, LogLevel.Information);
可观察生成的 SQL 是否高效利用了 JSONB 或数组操作。
3.4.2 使用批量操作（EF Core 7+）
与 SQL Server 类似，ExecuteUpdate/ExecuteDelete 可显著提升性能：
await context.Orders .Where(o => o.CreatedAt < DateTime.UtcNow.AddMonths(-6)) .ExecuteDeleteAsync();
3.4.3 连接池调优
Npgsql 默认启用连接池。可通过连接字符串调整：
Max Pool Size=100; Min Pool Size=5; Timeout=15;
在高并发微服务中，合理设置 Max Pool Size 避免数据库连接耗尽。
CREATE INDEX idx_preferences ON "Users" USING GIN ("Preferences");
在高并发微服务中，合理设置 Max Pool Size 避免数据库连接耗尽。
CREATE INDEX idx_preferences ON "Users" USING GIN ("Preferences");
- 对全文搜索字段使用 GIN(tsvector) 索引。
CREATE INDEX idx_tags ON "Products" USING GIN ("Tags");
3.5 与云服务集成（AWS RDS / Azure PostgreSQL）
- SSL 连接：生产环境必须启用 SSL。Npgsql 默认要求 SSL，可通过
Ssl Mode=Require 显式指定。 - 连接凭证管理：使用 AWS Secrets Manager 或 Azure Key Vault 存储密码。
- 只读副本：可通过配置多个 DbContext 实现读写分离（主库写，副本读）。
3.6 迁移注意事项
- 大小写敏感：PostgreSQL 默认将未加引号的标识符转为小写。EF Core 默认使用双引号包裹表名/列名以保留大小写（如
"Users"）。 - 序列命名：PostgreSQL 使用
SERIAL 或 IDENTITY 生成自增 ID，EF Core 迁移会自动创建序列（如 Entity_Id_seq）。 - 枚举映射
modelBuilder.Entity<User>() .Property(u => u.Status) .HasConversion<string>();
3.7 本章小结
PostgreSQL 凭借其丰富的数据类型、强大的扩展能力和开源生态，成为 .NET 开发者在追求灵活性、性能与成本平衡时的理想选择。通过 Npgsql 提供者，.NET 应用可以无缝利用 JSONB、数组、全文搜索等高级特性，构建现代、可扩展的应用系统。
第四章：SQLite —— 轻量级嵌入式数据库的极致体验
4.1 SQLite 简介与核心特性
SQLite 是一个零配置、无服务器、单文件的关系型数据库引擎。它将整个数据库（包括表、索引、触发器等）存储在一个跨平台的 .db 文件中，无需独立进程或网络连接。
核心优势：
- 零依赖部署：仅需一个 DLL（
Microsoft.Data.Sqlite）和一个 .db 文件。 - 极低资源消耗：内存占用小，适合嵌入式设备、移动应用、IoT 边缘节点。
- ACID 兼容
- 跨平台：Windows、Linux、macOS、Android、iOS 均原生支持。
- 开发效率高
局限性：
- 并发写入性能弱
- 功能精简：不支持外键级联删除（默认关闭）、存储过程、用户权限管理、某些高级 SQL 特性。
- 类型系统动态：采用“类型亲和性”（type affinity），非严格强类型。
在 .NET 生态中，SQLite 常用于：
- 本地开发与测试：替代 SQL Server/PostgreSQL 进行单元/集成测试。
- 桌面应用（如 WPF、WinForms、MAUI）：存储用户配置、缓存数据。
- 移动端应用（Xamarin、.NET MAUI）：离线数据持久化。
- 边缘计算/物联网设备
- CI/CD 流水线中的快速测试
4.2 在 .NET 中配置 SQLite 提供者
4.2.1 安装 NuGet 包
dotnet add package Microsoft.EntityFrameworkCore.Sqlite
此包包含 Microsoft.Data.Sqlite 驱动和 EF Core 提供者。
builder.Services.AddDbContext<AppDbContext>(options => options.UseSqlite(builder.Configuration.GetConnectionString("SqliteConnection")));
{ "ConnectionStrings": { "SqliteConnection": "Data Source=app.db" }}
连接字符串说明：
Data Source=app.dbMode=ReadWriteCreateCache=SharedForeign Keys=True
4.3 SQLite 在开发与测试中的典型用法
4.3.1 作为单元测试的内存数据库
SQLite 支持 :memory: 模式，创建纯内存数据库，非常适合隔离测试：
public class UserServiceTests{ [Fact] public async Task GetUser_ReturnsUser_WhenExists() { var options = new DbContextOptionsBuilder<AppDbContext>() .UseSqlite("Data Source=:memory:") .Options; using var context = new AppDbContext(options); context.Database.OpenConnection(); context.Database.EnsureCreated(); context.Users.Add(new User { Id = 1, Name = "Alice" }); await context.SaveChangesAsync(); var service = new UserService(context); var user = await service.GetUserAsync(1); Assert.Equal("Alice", user.Name); }}
✅ 优势：测试速度快、完全隔离、无外部依赖。
❌ 注意：内存数据库在连接关闭后销毁，不能跨 DbContext 实例共享。
4.3.2 本地开发时替代生产数据库
在 appsettings.Development.json 中切换为 SQLite：
{ "ConnectionStrings": { "DefaultConnection": "Data Source=dev.db" }}
配合条件编译或环境变量，实现开发/生产环境自动切换：
if (builder.Environment.IsDevelopment()){ builder.Services.AddDbContext<AppDbContext>(options => options.UseSqlite(builder.Configuration.GetConnectionString("DevConnection")));}else{ builder.Services.AddDbContext<AppDbContext>(options => options.UseNpgsql(builder.Configuration.GetConnectionString("ProdConnection")));}
⚠️ 警告：不要在生产环境用 SQLite 替代 PostgreSQL/SQL Server！仅用于开发模拟。
4.4 SQLite 特有行为与注意事项
4.4.1 外键默认禁用
SQLite 默认不启用外键约束。必须在连接字符串中显式开启：
Data Source=app.db; Foreign Keys=True;
PRAGMA foreign_keys = ON;
EF Core 的 OnModelCreating 中定义的外键关系，在 SQLite 中只有开启此选项才生效。
4.4.2 自增主键与 ROWID
SQLite 表隐含 ROWID 列。若定义 INTEGER PRIMARY KEY，则该列成为 ROWID 的别名，自动递增。
public class LogEntry{ public int Id { get; set; } public string Message { get; set; }}
CREATE TABLE "LogEntries" ( "Id" INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, "Message" TEXT NULL);
注意：AUTOINCREMENT 关键字在 SQLite 中非必需，仅用于防止 ID 重用（性能略低）。EF Core 默认不加，除非显式配置。
4.4.3 日期时间存储
SQLite 无原生 DATETIME 类型，通常存储为 TEXT（ISO8601）或 INTEGER（Unix 时间戳）。
EF Core 默认映射 DateTime 为 TEXT：
"CreatedAt" TEXT NOT NULL
值示例："2025-11-08 11:42:00"
可通过值转换器自定义：
modelBuilder.Entity<Event>() .Property(e => e.Timestamp) .HasConversion<long>();
4.4.4 不支持的 EF Core 功能
以下功能在 SQLite 提供者中部分或完全不支持：
ExecuteUpdate / ExecuteDelete（EF Core 7+）：SQLite 不支持 UPDATE ... FROM 或复杂 WHERE 子查询，可能回退到客户端评估。- 某些 LINQ 方法：如
GroupBy 后直接 Select 复杂对象，可能触发客户端评估警告。
建议：在 SQLite 上运行前，先用 ToQueryString() 检查生成的 SQL 是否合理。
4.5 性能与部署优化
4.5.1 WAL 模式提升并发读
默认的 DELETE 日志模式在写入时会阻塞所有读。启用 WAL（Write-Ahead Logging） 可显著提升读并发：
context.Database.ExecuteSqlRaw("PRAGMA journal_mode=WAL;");
Data Source=app.db; Mode=ReadWrite; Cache=Shared; Journal Mode=WAL;
WAL 模式下，读操作可与写操作并发，但需注意文件锁机制（尤其在 NFS 等网络文件系统上）。
4.5.2 文件路径管理
- 桌面应用：建议将
.db 文件存于 Environment.GetFolderPath(Environment.SpecialFolder.ApplicationData)。 - Web 应用：确保
wwwroot 外且有写权限（如 App_Data 目录）。 - MAUI/Xamarin：使用
FileSystem.AppDataDirectory 获取安全存储路径。
示例（.NET MAUI）：
string dbPath = Path.Combine(FileSystem.AppDataDirectory, "myapp.db");options.UseSqlite($"Data Source={dbPath}");
4.5.3 加密支持（SQLCipher）
标准 SQLite 不支持加密。若需保护数据，可集成 SQLCipher（开源版需自行编译，商业版付费）。
通过 Microsoft.Data.Sqlite.Core + 自定义 SqliteConnectionFactory 实现，但超出本章范围。
4.6 本章小结
SQLite 以其极简、嵌入式、零配置的特性，成为 .NET 开发中不可或缺的工具。它虽不适合高并发生产环境，但在本地开发、自动化测试、桌面/移动应用等场景中表现出色。正确理解其行为差异（如外键、类型系统、并发模型），并合理配置连接选项，能极大提升开发效率与应用稳定性。
第五章：跨数据库兼容设计 —— 编写真正可移植的 .NET 数据访问层
5.1 为什么需要数据库无关性？
尽管 SQL 标准存在，但各大数据库在数据类型、函数、语法、事务行为等方面差异显著。若代码强依赖某一数据库特性（如 SQL Server 的 GETDATE() 或 PostgreSQL 的 JSONB），则迁移成本极高。
实现数据库无关性（Database Agnosticism）的目标不是“一套代码跑所有库”，而是：
- 降低切换成本
- 支持多租户多后端
- 提升测试覆盖率：用 SQLite 快速测试核心逻辑，再用 PostgreSQL 验证高级功能。
- 符合架构原则
📌 关键理念：80% 的通用逻辑应与数据库解耦，20% 的特有功能可条件启用。
5.2 设计原则：抽象与封装
5.2.1 避免原始 SQL 泄露到业务层
❌ 反模式：
var users = context.Users.FromSqlRaw("SELECT * FROM \"Users\" WHERE ...");
✅ 正确做法：
- 将数据库特定逻辑封装在 Repository 或 EF Core 扩展方法中。
5.2.2 使用 EF Core 的通用 API
优先使用跨提供者兼容的 LINQ 和 EF Core 方法：
Where, Select, OrderBy, GroupByIncludeAddSaveChangesAsync
避免：
FromSqlRaw- 数据库特定函数（如
EF.Functions.DateDiffDay 仅 SQL Server 支持）
5.3 条件编译与运行时适配
5.3.1 按提供者类型动态配置
在 OnModelCreating 中检测当前数据库类型：
protected override void OnModelCreating(ModelBuilder modelBuilder){ base.OnModelCreating(modelBuilder); if (Database.IsSqlServer()) { modelBuilder.Entity<Order>() .Property(o => o.Total) .HasComputedColumnSql("[Price] * [Quantity]"); } else if (Database.IsNpgsql()) { modelBuilder.Entity<User>() .Property(u => u.Preferences) .HasColumnType("jsonb"); } else if (Database.IsSqlite()) { modelBuilder.Entity<Log>().Property(l => l.Timestamp).HasConversion<long>(); }}
✅ 优势：同一 DbContext 支持多数据库，无需维护多个上下文类。
5.3.2 自定义数据库函数映射（EF Core 7+）
通过 DbFunction 注册跨数据库兼容的函数：
public static class DbFunctionsExtensions{ public static DateTime GetCurrentDateTime(this DbFunctions _) => throw new NotSupportedException(); protected override void OnModelCreating(ModelBuilder modelBuilder) { if (Database.IsSqlServer()) { modelBuilder.HasDbFunction(typeof(DbFunctionsExtensions).GetMethod(nameof(GetCurrentDateTime))) .HasTranslation(args => new SqlFunctionExpression("GETDATE", typeof(DateTime))); } else if (Database.IsNpgsql()) { modelBuilder.HasDbFunction(typeof(DbFunctionsExtensions).GetMethod(nameof(GetCurrentDateTime))) .HasTranslation(args => new SqlFunctionExpression("NOW", typeof(DateTime))); } }}
var now = context.Users.Select(u => EF.Functions.GetCurrentDateTime()).First();
⚠️ 注意：过度使用会增加复杂度，建议仅用于高频核心函数。
5.4 迁移策略：如何管理多数据库迁移？
5.4.1 单一迁移 vs 多迁移目录
方案一：单一迁移（推荐用于简单场景）
EF Core 会根据当前配置的提供者生成对应 SQL。但需注意：
方案二：多迁移目录（推荐用于复杂系统）
为每种数据库维护独立迁移目录：
dotnet ef migrations add InitialCreate dotnet ef migrations add InitialCreate dotnet ef migrations add InitialCreate
string provider = builder.Configuration["Database:Provider"] ?? "SqlServer";if (provider == "PostgreSql") services.AddDbContext<AppDbContext>(opt => opt.UseNpgsql(connStr));else if (provider == "Sqlite") services.AddDbContext<AppDbContext>(opt => opt.UseSqlite(connStr));
💡 提示：可在 CI/CD 中为每种数据库运行集成测试，确保迁移兼容性。
5.4.2 使用 IDesignTimeDbContextFactory 支持多环境迁移
创建工厂类，根据环境变量生成对应 DbContext：
public class AppDbContextFactory : IDesignTimeDbContextFactory<AppDbContext>{ public AppDbContext CreateDbContext(string[] args) { var config = new ConfigurationBuilder() .AddEnvironmentVariables() .Build(); var optionsBuilder = new DbContextOptionsBuilder<AppDbContext>(); var provider = config["DB_PROVIDER"] ?? "SqlServer"; if (provider == "PostgreSql") optionsBuilder.UseNpgsql(config["CONNECTION_STRING"]); else if (provider == "Sqlite") optionsBuilder.UseSqlite(config["CONNECTION_STRING"]); else optionsBuilder.UseSqlServer(config["CONNECTION_STRING"]); return new AppDbContext(optionsBuilder.Options); }}
$env:DB_PROVIDER="PostgreSql"$env:CONNECTION_STRING="Host=..."dotnet ef migrations add AddPreferences --project src/App.Data
5.5 测试策略：构建多数据库测试矩阵
5.5.1 分层测试架构
| | |
|---|
| | |
| Docker 容器（SQL Server / PostgreSQL） | |
| | |
5.5.2 使用 Testcontainers 简化集成测试
Testcontainers 可在测试中自动启动数据库容器：
public class PostgreSqlIntegrationTests : IAsyncLifetime{ private PostgreSqlContainer _container; private AppDbContext _context; public async Task InitializeAsync() { _container = new PostgreSqlBuilder() .WithImage("postgres:15") .Build(); await _container.StartAsync(); var options = new DbContextOptionsBuilder<AppDbContext>() .UseNpgsql(_container.GetConnectionString()) .Options; _context = new AppDbContext(options); await _context.Database.MigrateAsync(); } [Fact] public async Task CanQueryUser() { _context.Users.Add(new User { Name = "Test" }); await _context.SaveChangesAsync(); var user = await _context.Users.FirstAsync(); Assert.Equal("Test", user.Name); } public async Task DisposeAsync() { await _container.DisposeAsync(); }}
✅ 支持 SQL Server、PostgreSQL、MySQL 等，确保测试环境与生产一致。
5.6 常见陷阱与规避方案
| | |
|---|
| | |
| | |
| SQL Server 迁移到 PostgreSQL 主键失败 | | 使用 ValueGeneratedOnAdd() 而非硬编码 |
| SQLite 存 TEXT，SQL Server 存 DATETIME2 | 统一用 DateTime.UtcNow，避免 .ToLocalTime() |
| N+1 查询在 SQLite 不报错但在 PG 报错 | | 开启 EnableDetailedErrors() 提前暴露问题 |
5.7 本章小结
跨数据库兼容并非追求“完全一致”，而是通过合理抽象、条件适配、自动化测试，在保持开发效率的同时保留技术选型灵活性。本章提供的策略——从 OnModelCreating 的运行时检测，到多迁移目录管理，再到基于 Testcontainers 的测试矩阵——构成了现代 .NET 应用数据层的坚实基础。
第六章：性能对比与选型指南 —— SQL Server vs PostgreSQL vs SQLite 实战压测分析
6.1 测试目标与环境说明
本章通过真实基准测试，对比三大数据库在 .NET 环境下的核心性能指标，帮助开发者根据业务场景做出合理选型。
测试目标：
测试环境：
- 硬件：Intel i7-12700H, 32GB RAM, NVMe SSD
- 操作系统：Windows 11 + WSL2（PostgreSQL 运行于 Ubuntu 22.04）
- .NET 版本
- 数据库版本：
- SQL Server 2022 Express（LocalDB）
- EF Core 版本
- 测试工具：BenchmarkDotNet + 自定义并发负载生成器
⚠️ 注意：测试结果受硬件、配置、数据规模影响，仅供参考。实际项目需结合自身场景压测。
6.2 测试模型设计
定义统一实体模型，确保公平比较：
public class Order{ public int Id { get; set; } public string CustomerName { get; set; } = default!; public decimal TotalAmount { get; set; } public DateTime CreatedAt { get; set; } public bool IsCompleted { get; set; }
public Dictionary<string, object>? Metadata { get; set; }}
创建索引：
CreatedAtIsCompletedTotalAmount
6.3 性能测试结果分析
6.3.1 单条插入性能（10,000 条，非事务）
✅ 结论：
- SQLite 内存模式最快
- 文件 SQLite 受磁盘 I/O 限制
- SQL Server 与 PostgreSQL 在单线程插入上性能接近。
💡 优化建议：无论哪种数据库，批量插入必须使用事务包裹！
6.3.2 批量插入性能（10,000 条，单事务）
✅ 结论：
6.3.3 简单查询性能（WHERE + LIMIT 100）
查询：SELECT * FROM Orders WHERE CreatedAt > '2025-01-01' AND IsCompleted = true ORDER BY Id LIMIT 100
✅ 结论：
6.3.4 复杂聚合查询（GROUP BY + SUM）
查询：SELECT CustomerName, SUM(TotalAmount) FROM Orders GROUP BY CustomerName
（数据量：100,000 行，1,000 个客户）
✅ 结论：
6.3.5 并发读写能力（100 并发线程，混合负载）
模拟：70% 读（查询），20% 写（插入），10% 更新
✅ 结论：
- SQLite 不适合高并发写入场景
- PostgreSQL 与 SQL Server 在并发处理上表现卓越
6.4 资源占用对比
✅ 结论：
- SQLite 是资源受限环境（如 IoT、移动端）的唯一选择。
- 云原生应用推荐 PostgreSQL
- 已有 Windows/.NET 生态投资可选 SQL Server。
6.6 真实项目选型案例
案例 1：SaaS 多租户平台
- 需求：支持 10,000+ 租户，部分租户要求独立数据库，需 JSON 存储动态表单。
- 选型：PostgreSQL
- 理由：JSONB 高效、Schema 隔离灵活、成本低、支持逻辑复制。
案例 2：银行内部审批系统
- 需求：强事务、审计日志、与 Active Directory 集成、已有 SQL Server DBA 团队。
- 选型：SQL Server
- 理由：企业级支持、Always On 高可用、SSRS 报表无缝集成。
案例 3：.NET MAUI 离线笔记 App
6.7 本章小结
性能并非选型唯一标准，需综合考虑功能需求、团队技能、运维成本、扩展性。本章通过实测数据表明：
- SQLite
- PostgreSQL
- SQL Server
📌 最终建议：新项目优先评估 PostgreSQL；已有 SQL Server 投资可延续；SQLite 用于特定场景而非生产主库。
第七章：高级实战项目 Demo —— 构建一个多数据库兼容的订单管理系统
本章将通过一个完整的 .NET 8 Web API 项目，演示如何在真实场景中实现 SQL Server、PostgreSQL 和 SQLite 的无缝切换，涵盖架构设计、数据访问层抽象、迁移管理、自动化测试与部署配置。
💡 项目代码结构遵循 Clean Architecture + Repository 模式，确保高内聚、低耦合。
7.1 项目需求概述
构建一个简化的订单管理系统，支持：
- 记录订单元数据（如配送方式、优惠券等，使用 JSON 存储）
- 单元测试（SQLite 内存） + 集成测试（PostgreSQL/SQL Server）
OrderSystem/├── src/│ ├── OrderSystem.Api/ │ ├── OrderSystem.Core/ │ ├── OrderSystem.Infrastructure/ │ └── OrderSystem.Application/ ├── tests/│ ├── OrderSystem.UnitTests/ │ └── OrderSystem.IntegrationTests/ └── docker-compose.yml
7.3 核心代码实现
7.3.1 领域模型（OrderSystem.Core）
public class Order{ public int Id { get; set; } public string CustomerName { get; set; } = default!; public decimal TotalAmount { get; set; } public DateTime CreatedAt { get; set; } public OrderStatus Status { get; set; }
public Dictionary<string, object>? Metadata { get; set; }}public enum OrderStatus{ Pending, Confirmed, Shipped, Cancelled}
7.3.2 DbContext 与多数据库适配（Infrastructure）
public class AppDbContext : DbContext{ public DbSet<Order> Orders => Set<Order>(); private readonly string? _databaseProvider; public AppDbContext(DbContextOptions<AppDbContext> options, IConfiguration config) : base(options) { _databaseProvider = config["Database:Provider"]; } protected override void OnModelCreating(ModelBuilder modelBuilder) { base.OnModelCreating(modelBuilder); if (_databaseProvider == "PostgreSql") { modelBuilder.Entity<Order>() .Property(o => o.Metadata) .HasColumnType("jsonb"); } else { modelBuilder.Entity<Order>() .Property(o => o.Metadata) .HasColumnType("nvarchar(max)"); } modelBuilder.Entity<Order>() .HasQueryFilter(o => o.Status != OrderStatus.Cancelled); }}
var builder = WebApplication.CreateBuilder(args);var provider = builder.Configuration["Database:Provider"] ?? "Sqlite";builder.Services.AddDbContext<AppDbContext>((sp, options) =>{ var connStr = sp.GetRequiredService<IConfiguration>() .GetConnectionString($"{provider}Connection"); if (provider == "PostgreSql") options.UseNpgsql(connStr); else if (provider == "SqlServer") options.UseSqlServer(connStr); else options.UseSqlite(connStr);});builder.Services.AddScoped<IOrderRepository, OrderRepository>();builder.Services.AddMediatR(cfg => cfg.RegisterServicesFromAssemblyContaining<Program>());
{ "Database": { "Provider": "PostgreSql" }, "ConnectionStrings": { "PostgreSqlConnection": "Host=localhost;Database=orders;Username=postgres;Password=pass", "SqlServerConnection": "Server=(localdb)\\mssqllocaldb;Database=OrdersDb;Trusted_Connection=true;", "SqliteConnection": "Data Source=orders.db;Foreign Keys=True;" }}
✅ 通过修改 Database:Provider 环境变量即可切换数据库！
public class OrderRepository : IOrderRepository{ private readonly AppDbContext _context; public OrderRepository(AppDbContext context) => _context = context; public async Task<List<Order>> GetRecentConfirmedOrdersAsync(DateTime since) { return await _context.Orders .Where(o => o.Status == OrderStatus.Confirmed && o.CreatedAt >= since) .OrderByDescending(o => o.CreatedAt) .ToListAsync(); } public async Task AddAsync(Order order) { _context.Orders.Add(order); await _context.SaveChangesAsync(); }}
所有 LINQ 查询均使用 EF Core 通用 API，避免原始 SQL。
7.5 迁移管理策略
为每种数据库维护独立迁移目录：
# 生成 SQL Server 迁移dotnet ef migrations add InitialCreate --output-dir Migrations/SqlServer --context AppDbContext# 生成 PostgreSQL 迁移dotnet ef migrations add InitialCreate --output-dir Migrations/PostgreSql --context AppDbContext -- --provider Npgsql# 生成 SQLite 迁移dotnet ef migrations add InitialCreate --output-dir Migrations/Sqlite --context AppDbContext -- --provider Sqlite
在 AppDbContext 中动态加载迁移程序集（可选），或通过 CI 脚本控制。
7.6 自动化测试
7.6.1 单元测试（SQLite 内存）
[Fact]public async Task CreateOrder_ShouldPersistMetadata(){ var options = new DbContextOptionsBuilder<AppDbContext>() .UseSqlite("Data Source=:memory:") .Options; using var context = new AppDbContext(options, new ConfigurationBuilder().Build()); context.Database.OpenConnection(); context.Database.EnsureCreated(); var repo = new OrderRepository(context); var order = new Order { CustomerName = "Alice", TotalAmount = 99.9m, CreatedAt = DateTime.UtcNow, Status = OrderStatus.Pending, Metadata = new() { ["coupon"] = "SAVE10" } }; await repo.AddAsync(order); var saved = await context.Orders.FirstAsync(); Assert.Equal("SAVE10", saved.Metadata?["coupon"]);}
7.6.2 集成测试（Testcontainers）
public class PostgreSqlOrderTests : IAsyncLifetime{ private PostgreSqlContainer _db; private AppDbContext _context; public async Task InitializeAsync() { _db = new PostgreSqlBuilder().Build(); await _db.StartAsync(); var options = new DbContextOptionsBuilder<AppDbContext>() .UseNpgsql(_db.GetConnectionString()) .Options; _context = new AppDbContext(options, new ConfigurationBuilder().Build()); await _context.Database.MigrateAsync(); } [Fact] public async Task CanQueryByMetadata() { _context.Database.ExecuteSqlRaw(@" INSERT INTO ""Orders"" (""CustomerName"", ""TotalAmount"", ""CreatedAt"", ""Status"", ""Metadata"") VALUES ('Bob', 50, NOW(), 1, '{{""source"":""mobile""}}'::jsonb) "); var result = _context.Orders .FromSqlRaw(@"SELECT * FROM ""Orders"" WHERE ""Metadata""->>'source' = 'mobile'") .ToList(); Assert.Single(result); } public async Task DisposeAsync() => await _db.DisposeAsync();}
7.7 部署与运维
7.7.1 Dockerfile（多阶段构建）
FROM mcr.microsoft.com/dotnet/sdk:8.0 AS buildWORKDIR /srcCOPY . .RUN dotnet publish -c Release -o /appFROM mcr.microsoft.com/dotnet/aspnet:8.0WORKDIR /appCOPY --from=build /app .ENTRYPOINT ["dotnet", "OrderSystem.Api.dll"]
7.7.2 Kubernetes / Azure App Service 配置
通过环境变量指定数据库：
env: - name: Database__Provider value: "PostgreSql" - name: ConnectionStrings__PostgreSqlConnection valueFrom: secretKeyRef: name: db-secret key: connection-string
7.8 项目亮点总结
- ✅ 运行时数据库切换：通过配置驱动，无需重新编译。
- ✅ JSON 兼容处理：PostgreSQL 用
jsonb，其他用 TEXT/nvarchar(max)。 - ✅ 测试全覆盖：单元测试（快）+ 集成测试（真）。
- ✅ 生产就绪：支持 Docker、K8s、云部署。
- ✅ 无供应商锁定：未来可轻松迁移到 MySQL 或 Oracle（扩展提供者即可）。
7.9 本章小结
本章通过一个真实订单系统，完整展示了如何在 .NET 中构建多数据库兼容、可测试、可部署的应用。核心在于：
- 抽象数据访问逻辑
- 条件化数据库特有功能
- 自动化测试保障质量
- 配置驱动而非代码硬编码
这种架构不仅提升了系统的灵活性，也为未来的演进打下坚实基础。
阅读原文：https://mp.weixin.qq.com/s/Lt67ktg_sHrKpFveq42oKQ
该文章在 2025/11/10 15:41:56 编辑过
400 186 1886
