c# - 如何对随时间变化的表格数据进行建模并能够回溯到任何点

Question

注意:这也被标记为 SQL，因为我完全接受 MVC 之外可能的脚本选项

我有一个使用 Entity Framework 、Code First/MySQL 的 C# MVC Razor 项目

想象一下这个场景:

public class RelationshipStatus
{
    public string status { get; set; }
}

上个月的状态是“单例”，这个月的状态是“已婚”。

我应该如何构建它以便我可以:

• 获取RelationshipStatus以及上个月的日期，看看当时是什么。

• 获取关系状态所有更改的时间表。

这个项目有很多表，但条目很少，而且只有少数用户。我更关心此解决方案的特异性而不是性能。

到目前为止我的尝试和想法:

• 用于捕获表更新并将其发布到日志记录表的 SQL 触发器。
• 重写 SaveChanges() 以插入我自己的日志记录代码。
• 使用 Tracker-Enabled-DBContext，这会产生一个错误，所有者称该错误永远不会修复。
• 为每个模型创建 EntityID、VersionID 和日期。插入新行而不是在编辑时更新并增加 VersionID。

Answer 1

您是否考虑过使用event sourcing ？通常与 CQRS 一起使用.

来自Wikipedia :

An architectural pattern which warrants that your entities (as per Eric Evans’ definition) do not track their internal state by means of direct serialization or O/R mapping, but by means of reading and committing events to an event store. Where ES is combined with CQRS and DDD, aggregate roots are responsible for thoroughly validating and applying commands (often by means having their instance methods invoked from a Command Handler), and then publishing a single or a set of events which is also the foundation upon which the aggregate roots base their logic for dealing with method invocations. Hence, the input is a command and the output is one or many events which are transactionally (single commit) saved to an event store, and then often published on a message broker for the benefit of those interested (often the views are interested; they are then queried using Query-messages). When modeling your aggregate roots to output events, you can isolate the internal state event further than would be possible when projecting read-data from your entities, as is done in standard n-tier data-passing architectures. One significant benefit from this is that tooling such as axiomatic theorem provers (e.g. Microsoft Contracts or CHESS) are easier to apply, as the aggregate root comprehensively hides its internal state. Events are often persisted based on the version of the aggregate root instance, which yields a domain model that synchronizes in distributed systems around the concept of optimistic concurrency

通过事件溯源，您可以跟踪聚合的所有更改，并查看对象在特定日期/版本上的确切状态。