如何使用EF Core 8实现对象一对多拆分与版本化存储？

问题概述 #

需将CatalogRuleApp拆分为两张数据库表：RuleApp存储应用元数据，Revisions存储多版本的JSON状态（一对多关系）。核心诉求：

• RuleApp变更时自动将JSON状态存入Revisions表
• 检索时将指定版本与元数据合并为单个CatalogRuleApp对象
• 获取EF Core 8下的DbContext配置指导，以及更优实现方案建议

数据库表结构 #

RuleApps表 #

Revisions表 #

实体代码（已翻译） #

interface IRevision
{
    public List<RuleFolder> Rules { get; set; }
    public List<Entity> Entities { get; set; }
    public List<DataElement> DataElements { get; set; }
}

public abstract class RuleApp : Entity, IRevision
{
    public string Name { get; set; }
    public DateTime CreatedOn { get; set; }
    public List<RuleFolder> Rules { get; set; }
    public List<Entity> Entities { get; set; }
    public List<DataElement> DataElements { get; set; }
}

public sealed class CatalogRuleApp : RuleApp
{
    public RevisionInfo Revision { get; set; }
    // 其他属性... //
}

public class RevisionInfo : Entity
{
    public int Revision { get; set; }
    public CheckIn CheckedIn { get; set; }
    public CheckOut CheckedOut { get; set; }

    public RuleApp RuleApp { get; set; } // 补充导航属性，EF Core需要它维护关系

    public string RevisionJsonState
    {
        get => JsonConvert.SerializeObject((IRevision)RuleApp);
        set
        {
            if (string.IsNullOrEmpty(value))
                return;

            var revision = JsonConvert.DeserializeObject<IRevision>(value);
        
            if (RuleApp != null && revision != null)
            {
                RuleApp.Rules = revision.Rules;
                RuleApp.Entities = revision.Entities;
                RuleApp.DataElements = revision.DataElements;
            }
        }
    }
}

public class CheckIn : Entity
{
    public User User { get; set; }
    public DateTime DateTime { get; set; }
    public string? Comment { get; set; }
}

public class CheckOut : Entity
{
    public User User { get; set; }
    public DateTime DateTime { get; set; }
    public string? Comment { get; set; }
}

DbContext配置指导 #

1. 实体关系与表映射配置 #

在DbContext中明确表结构、主键和一对多关系：

public class AppDbContext : DbContext
{
    public DbSet<RuleApp> RuleApps { get; set; }
    public DbSet<RevisionInfo> Revisions { get; set; }

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        // RuleApp表配置
        modelBuilder.Entity<RuleApp>()
            .ToTable("RuleApps")
            .HasKey(r => r.Id);

        modelBuilder.Entity<RuleApp>()
            .Property(r => r.Name)
            .IsRequired();

        modelBuilder.Entity<RuleApp>()
            .Property(r => r.CreatedOn)
            .HasColumnName("Created On");

        // Revisions表配置
        modelBuilder.Entity<RevisionInfo>()
            .ToTable("Revisions")
            .HasKey(r => new { r.RuleAppId, r.Revision }); // 复合主键：RuleAppId+版本号

        modelBuilder.Entity<RevisionInfo>()
            .Property(r => r.RevisionJsonState)
            .IsRequired()
            .HasColumnName("JsonSerializedState");

        // 一对多关系：RuleApp -> RevisionInfo
        modelBuilder.Entity<RuleApp>()
            .HasMany<RevisionInfo>()
            .WithOne(r => r.RuleApp)
            .HasForeignKey(r => r.RuleAppId)
            .OnDelete(DeleteBehavior.Cascade);

        // CheckIn/CheckOut作为独立实体配置（若不需要单独查询，可改为Owned Entity）
        modelBuilder.Entity<CheckIn>()
            .HasKey(c => c.Id);

        modelBuilder.Entity<CheckOut>()
            .HasKey(c => c.Id);

        modelBuilder.Entity<RevisionInfo>()
            .HasOne(r => r.CheckedIn)
            .WithOne()
            .HasForeignKey<CheckIn>(c => c.Id);

        modelBuilder.Entity<RevisionInfo>()
            .HasOne(r => r.CheckedOut)
            .WithOne()
            .HasForeignKey<CheckOut>(c => c.Id);
    }
}

2. 变更时自动生成版本 #

重写DbContextContext的SaveChangesAsync方法，检测RuleApp变更并自动创建新版本：

public override async Task<int> SaveChangesAsync(CancellationToken cancellationToken = default)
{
    var changedRuleApps = ChangeTracker.Entries<RuleApp>()
        .Where(e => e.State is EntityState.Modified or EntityState.Added)
        .Select(e => e.Entity)
        .ToList();

    foreach (var ruleApp in changedRuleApps)
    {
        // 获取当前RuleApp的最新版本号
        var latestRevision = await Revisions
            .Where(r => r.RuleAppId == ruleApp.Id)
            .OrderByDescending(r => r.Revision)
            .FirstOrDefaultAsync(cancellationToken);

        int newRevisionNumber = latestRevision?.Revision + 1 ?? 1;

        // 创建新版本记录
        var newRevision = new RevisionInfo
        {
            RuleAppId = ruleApp.Id,
            Revision = newRevisionNumber,
            RuleApp = ruleApp,
            CheckedIn = new CheckIn
            {
                User = /* 注入IHttpContextAccessor获取当前用户 */,
                DateTime = DateTime.UtcNow,
                Comment = "自动保存版本"
            }
        };

        Revisions.Add(newRevision);
    }

    return await base.SaveChangesAsync(cancellationToken);
}

3. 检索指定版本并合并为CatalogRuleApp #

编写查询方法，合并元数据与指定版本的状态：

public async Task<CatalogRuleApp?> GetCatalogRuleAppByRevisionAsync(int ruleAppId, int revisionNumber)
{
    var ruleApp = await RuleApps.FirstOrDefaultAsync(r => r.Id == ruleAppId);
    if (ruleApp == null) return null;

    var revision = await Revisions
        .Include(r => r.CheckedIn)
        .Include(r => r.CheckedOut)
        .FirstOrDefaultAsync(r => r.RuleAppId == ruleAppId && r.Revision == revisionNumber);
    if (revision == null) return null;

    // 触发反序列化，将JSON状态同步到RuleApp
    revision.RevisionJsonState = revision.RevisionJsonState;

    return new CatalogRuleApp
    {
        Id = ruleApp.Id,
        Name = ruleApp.Name,
        CreatedOn = ruleApp.CreatedOn,
        Rules = ruleApp.Rules,
        Entities = ruleApp.Entities,
        DataElements = ruleApp.DataElements,
        Revision = revision
    };
}

优化建议与替代方案 #

1. 使用Owned Entities简化CheckIn/CheckOut #

若CheckIn/CheckOut不需要独立查询，可将其配置为Owned Entities，直接嵌入Revisions表：

modelBuilder.Entity<RevisionInfo>()
    .OwnsOne(r => r.CheckedIn, cb =>
    {
        cb.Property(c => c.DateTime).HasColumnName("CheckInDateTime");
        cb.Property(c => c.Comment).HasColumnName("CheckInComment");
    });

modelBuilder.Entity<RevisionInfo>()
    .OwnsOne(r => r.CheckedOut, cb =>
    {
        cb.Property(c => c.DateTime).HasColumnName("CheckOutDateTime");
        cb.Property(c => c.Comment).HasColumnName("CheckOutComment");
    });

2. 利用EF Core原生JSON列支持 #

若数据库支持JSON列（SQL Server 2016+/PostgreSQL），可直接将IRevision映射为JSON列，避免手动序列化：

// 在RevisionInfo中新增属性
public IRevision RevisionState { get; set; }

// 配置映射
modelBuilder.Entity<RevisionInfo>()
    .Property(r => r.RevisionState)
    .HasColumnType("jsonb") // PostgreSQL用jsonb，SQL Server用nvarchar(max)
    .HasConversion(
        v => JsonConvert.SerializeObject(v),
        v => JsonConvert.DeserializeObject<IRevision>(v)!
    );

3. 版本号性能优化 #

在RuleApp表中新增CurrentRevision字段，每次创建新版本时更新该值，查询最新版本时无需排序：

// RuleApp类新增属性
public int CurrentRevision { get; set; }

// SaveChangesAsync中更新
ruleApp.CurrentRevision = newRevisionNumber;

4. 历史版本归档 #

对于老旧版本，可定期迁移到Blob存储（如Azure Blob、AWS S3），减少数据库存储压力，仅保留最近N个版本在数据库中。

内容的提问来源于stack exchange，提问作者Furqan Safdar