EF Core 数据访问

public class ShopDbContext(DbContextOptions<ShopDbContext> options)
    : DbContext(options)
{
    public DbSet<Order> Orders => Set<Order>();
    public DbSet<Customer> Customers => Set<Customer>();
    public DbSet<OrderLine> OrderLines => Set<OrderLine>();
}

// 注册：Scoped 生命周期，每个请求一个上下文
builder.Services.AddDbContext<ShopDbContext>(options =>
    options.UseSqlServer(builder.Configuration
        .GetConnectionString("Default")));

// 注入使用
public class OrderService(ShopDbContext db)
{
    public async Task<List<Order>> GetRecentAsync() =>
        await db.Orders.OrderByDescending(o => o.CreatedAt)
                       .Take(10)
                       .ToListAsync();
}

public class Order
{
    public int Id { get; set; }                    // 约定：主键
    public int CustomerId { get; set; }            // 约定：外键
    public Customer? Customer { get; set; }        // 导航属性
    public decimal Total { get; set; }
    public DateTime CreatedAt { get; set; }
}

public class Customer
{
    public int Id { get; set; }
    public string Name { get; set; } = string.Empty;
    public List<Order> Orders { get; set; } = [];  // 一对多
}

// Fluent API 显式配置：索引、精度、外键行为
protected override void OnModelCreating(ModelBuilder modelBuilder)
{
    modelBuilder.Entity<Order>(entity =>
    {
        entity.HasIndex(o => o.CustomerId);
        entity.Property(o => o.Total).HasPrecision(18, 2);
        entity.HasOne(o => o.Customer)
              .WithMany(c => c.Orders)
              .HasForeignKey(o => o.CustomerId)
              .OnDelete(DeleteBehavior.Restrict);
    });
}

# 生成迁移（dll 需能加载模型）
dotnet ef migrations add AddOrderIndex
# 本地应用
dotnet ef database update
# 生成 SQL 脚本（上线审阅用）
dotnet ef migrations script --from 0 --output up.sql

// 生成的迁移快照：Up 与 Down 成对
public partial class AddOrderIndex : Migration
{
    protected override void Up(MigrationBuilder migrationBuilder)
    {
        migrationBuilder.CreateIndex(
            name: "IX_Orders_CustomerId",
            table: "Orders",
            column: "CustomerId");
    }

    protected override void Down(MigrationBuilder migrationBuilder)
    {
        migrationBuilder.DropIndex(
            name: "IX_Orders_CustomerId",
            table: "Orders");
    }
}

// 可组合查询：先过滤后固化（IQueryable 翻译成 SQL）
var query = db.Orders
    .Include(o => o.Customer)
    .Where(o => o.Total > 100 && o.CreatedAt > since);

var list = await query.ToListAsync();

// 只读查询：AsNoTracking 省跟踪开销
var readonlyList = await db.Orders
    .AsNoTracking()
    .Where(o => o.Status == "Paid")
    .ToListAsync();

// 变更跟踪 + 保存
var order = await db.Orders.SingleAsync(o => o.Id == 1);
order.Total = 150m;                       // 标记为 Modified
await db.SaveChangesAsync();              // 生成 UPDATE

// 启用懒加载代理
builder.Services.AddDbContext<ShopDbContext>(options =>
    options.UseLazyLoadingProxies()
           .UseSqlServer(connectionString));

// 导航属性必须是 virtual 才能生成代理
public class Order
{
    public int Id { get; set; }
    public int CustomerId { get; set; }
    public virtual Customer? Customer { get; set; }   // virtual
}

// 显式加载：需要时明确查询
var order = await db.Orders.SingleAsync(o => o.Id == 1);
await db.Entry(order).Reference(o => o.Customer).LoadAsync();

// 反面：循环触发懒加载 → N+1
var orders = await db.Orders.Take(100).ToListAsync();
foreach (var o in orders)
{
    Console.WriteLine(o.Customer?.Name);   // 每次访问都发一条 SQL
}

// 正面：贪婪加载一次带出
var orders2 = await db.Orders
    .Include(o => o.Customer)
    .Take(100)
    .ToListAsync();

// 更优：投影只取需要的列
var dtos = await db.Orders
    .Select(o => new { o.Id, o.Total, CustomerName = o.Customer!.Name })
    .Take(100)
    .ToListAsync();

public class Product
{
    public int Id { get; set; }
    public string Name { get; set; } = string.Empty;
    public int Stock { get; set; }
    public byte[] RowVersion { get; set; } = [];   // 并发令牌
}

// Fluent 配置行版本
entity.Property(p => p.RowVersion).IsRowVersion();

// 冲突处理
try
{
    await db.SaveChangesAsync();
}
catch (DbUpdateConcurrencyException ex)
{
    // 冲突：重新读取或提示用户重试
    var entry = ex.Entries.Single();
    var dbValues = await entry.GetDatabaseValuesAsync();
    Console.WriteLine($"库存已被改为 {dbValues?.GetValue<int>("Stock")}");
}