From csharp
C# 数据访问与 ORM 规范:EF Core 8 compiled queries 编译查询、JSON columns、complex types 复杂类型、bulk operations 批量操作、interceptors 拦截器、数据库迁移。访问数据库、设计数据模型、优化查询性能时加载。
npx claudepluginhub lazygophers/ccplugin --plugin csharpThis skill uses the workspace's default tool permissions.
- **csharp:dev** - 数据层开发
Searches, retrieves, and installs Agent Skills from prompts.chat registry using MCP tools like search_skills and get_skill. Activates for finding skills, browsing catalogs, or extending Claude.
Searches prompts.chat for AI prompt templates by keyword or category, retrieves by ID with variable handling, and improves prompts via AI. Use for discovering or enhancing prompts.
Guides MCP server integration in Claude Code plugins via .mcp.json or plugin.json configs for stdio, SSE, HTTP types, enabling external services as tools.
public class AppDbContext(DbContextOptions<AppDbContext> options) : DbContext(options)
{
public DbSet<User> Users => Set<User>();
public DbSet<Order> Orders => Set<Order>();
protected override void OnModelCreating(ModelBuilder modelBuilder)
{
// Fluent API 配置
modelBuilder.Entity<User>(entity =>
{
entity.HasKey(x => x.Id);
entity.Property(x => x.Email).IsRequired().HasMaxLength(256);
entity.HasIndex(x => x.Email).IsUnique();
entity.HasQueryFilter(x => !x.IsDeleted); // 全局过滤(软删除)
});
// EF Core 8: JSON column
modelBuilder.Entity<User>()
.OwnsOne(u => u.Address, b => b.ToJson());
// EF Core 8: Complex type(值对象)
modelBuilder.Entity<Order>()
.ComplexProperty(o => o.ShippingAddress);
// 自动应用所有 IEntityTypeConfiguration
modelBuilder.ApplyConfigurationsFromAssembly(typeof(AppDbContext).Assembly);
}
}
// ✅ 预编译查询 - 避免每次重新编译表达式树
public class UserRepository(AppDbContext context)
{
// 静态编译查询
private static readonly Func<AppDbContext, int, CancellationToken, Task<User?>>
GetByIdQuery = EF.CompileAsyncQuery(
(AppDbContext db, int id, CancellationToken ct) =>
db.Users.AsNoTracking().FirstOrDefault(u => u.Id == id));
private static readonly Func<AppDbContext, string, CancellationToken, Task<User?>>
GetByEmailQuery = EF.CompileAsyncQuery(
(AppDbContext db, string email, CancellationToken ct) =>
db.Users.AsNoTracking().FirstOrDefault(u => u.Email == email));
public Task<User?> FindAsync(int id, CancellationToken ct = default)
=> GetByIdQuery(context, id, ct);
public Task<User?> FindByEmailAsync(string email, CancellationToken ct = default)
=> GetByEmailQuery(context, email, ct);
}
// ✅ ExecuteUpdate - 批量更新(不加载实体)
await context.Users
.Where(u => u.IsActive && u.LastLogin < DateTime.UtcNow.AddYears(-1))
.ExecuteUpdateAsync(s => s
.SetProperty(u => u.IsActive, false)
.SetProperty(u => u.DeactivatedAt, DateTime.UtcNow),
ct);
// ✅ ExecuteDelete - 批量删除
await context.Users
.Where(u => !u.IsActive && u.DeactivatedAt < DateTime.UtcNow.AddYears(-2))
.ExecuteDeleteAsync(ct);
// ✅ 批量插入(EF Core 8 优化)
var users = Enumerable.Range(0, 1000)
.Select(i => new User { Name = $"User_{i}", Email = $"user{i}@example.com" })
.ToList();
context.Users.AddRange(users);
await context.SaveChangesAsync(ct);
// ✅ 实体定义
public class User
{
public int Id { get; set; }
public string Name { get; set; } = "";
public Address Address { get; set; } = new();
public List<PhoneNumber> PhoneNumbers { get; set; } = [];
}
public record Address(string Street, string City, string ZipCode);
public record PhoneNumber(string Type, string Number);
// ✅ 查询 JSON 列内字段
var usersInBeijing = await context.Users
.Where(u => u.Address.City == "Beijing")
.ToListAsync(ct);
// ✅ 查询 JSON 数组
var usersWithMobile = await context.Users
.Where(u => u.PhoneNumbers.Any(p => p.Type == "Mobile"))
.ToListAsync(ct);
// ✅ AsNoTracking(只读查询)
var users = await context.Users
.AsNoTracking()
.Where(u => u.IsActive)
.ToListAsync(ct);
// ✅ Projection 减少数据传输
var userDtos = await context.Users
.Where(u => u.IsActive)
.Select(u => new UserDto(u.Id, u.Name, u.Email))
.ToListAsync(ct);
// ✅ Include + AsSplitQuery 避免笛卡尔积
var usersWithOrders = await context.Users
.Include(u => u.Orders)
.ThenInclude(o => o.Items)
.AsSplitQuery()
.ToListAsync(ct);
// ✅ 分页查询
var page = await context.Users
.OrderBy(u => u.Id)
.Skip((pageNumber - 1) * pageSize)
.Take(pageSize)
.ToListAsync(ct);
// ✅ 显式事务
public async Task TransferAsync(int fromId, int toId, decimal amount, CancellationToken ct)
{
var strategy = context.Database.CreateExecutionStrategy();
await strategy.ExecuteAsync(async () =>
{
await using var transaction = await context.Database.BeginTransactionAsync(ct);
try
{
var from = await context.Accounts.FindAsync([fromId], ct)
?? throw new NotFoundException($"Account {fromId} not found");
var to = await context.Accounts.FindAsync([toId], ct)
?? throw new NotFoundException($"Account {toId} not found");
from.Balance -= amount;
to.Balance += amount;
await context.SaveChangesAsync(ct);
await transaction.CommitAsync(ct);
}
catch
{
await transaction.RollbackAsync(ct);
throw;
}
});
}
// ✅ 自动设置审计字段
public class AuditInterceptor : SaveChangesInterceptor
{
public override ValueTask<InterceptionResult<int>> SavingChangesAsync(
DbContextEventData eventData,
InterceptionResult<int> result,
CancellationToken ct = default)
{
var context = eventData.Context!;
foreach (var entry in context.ChangeTracker.Entries<IAuditable>())
{
if (entry.State == EntityState.Added)
{
entry.Entity.CreatedAt = DateTime.UtcNow;
}
entry.Entity.UpdatedAt = DateTime.UtcNow;
}
return base.SavingChangesAsync(eventData, result, ct);
}
}
// 注册
builder.Services.AddDbContext<AppDb>((sp, o) =>
o.UseSqlServer(connectionString)
.AddInterceptors(new AuditInterceptor()));
# 创建迁移
dotnet ef migrations add AddUserAddress --project Infrastructure --startup-project WebApi
# 应用迁移
dotnet ef database update
# 生成 SQL 脚本(生产环境推荐)
dotnet ef migrations script --idempotent --output migration.sql
// ✅ 程序启动时自动迁移(开发环境)
if (app.Environment.IsDevelopment())
{
using var scope = app.Services.CreateScope();
var db = scope.ServiceProvider.GetRequiredService<AppDb>();
await db.Database.MigrateAsync();
}
| AI 可能的理性化解释 | 实际应该检查的内容 |
|---|---|
| "EF Core 自动优化" | ✅ 热路径是否用 compiled queries? |
| "Select * 方便" | ✅ 是否用 projection 只查需要的列? |
| "Include 一切" | ✅ 多集合 Include 是否用 AsSplitQuery? |
| "逐条更新没问题" | ✅ 批量操作是否用 ExecuteUpdate/Delete? |
| "tracking 无所谓" | ✅ 只读查询是否用 AsNoTracking? |
| "InMemory 测试够用" | ✅ 集成测试是否用 TestContainers? |
| "JSON 存 string 就行" | ✅ 是否用 EF Core 8 JSON columns? |
| "手动设置审计字段" | ✅ 是否用 SaveChanges interceptor? |