实体框架的快照历史记录
我一直在研究实体框架的一些审计挂钩。其中许多都显示了新旧价值的比较。这对于审计跟踪非常有用，但我正在寻找快照对象。
例如...假设我有一个管理产品的应用程序。产品具有多个属性和关联的其他对象。假设我改变一个对象 10 次。还可以说，重要的是我可以查看这些对象更改的屏幕（不是审计跟踪，而是只读格式的屏幕实际外观）。我感兴趣的是能够检索所有 10 个更改（取决于我想要查看的更改）的原始 EF 产品对象（以及所有关联数据），并使用它绑定到我的屏幕。
如果我使用 SQL Server，现在应该使用什么类型的序列化对象（XML、blob 等）？这样做有意义吗？
I've been looking at some auditing hooks with Entity Framework. Many of them show old/new value comparisons. This does great for an audit trail but I'm looking to snapshot objects.
For example... Let's say I have an application that manages products. A product has multiple attributes and associated other objects. Let's say I change an object 10 times. Let's also say that's important that I can view screens of those object changes (not an audit trail but what the screen actually looked like in a read only format). What I'm interested in is being able to retrieve the original EF product object (with all of the associated data) for all 10 of those changes (depending on which I want to see)and use that to bind to my screen.
If I'm using SQL Server, what type should I use for the serialized object nowadays (XML, blob, etc)? Does it make sense to do this?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（4）
让我们来看看。您需要获取一个对象图并将其序列化到数据库中，格式允许您稍后将其具体化。我认为有一些工具可以做到这一点。令我印象深刻的是，其中之一就是实体框架。
你想做的事情是一件很平常的事情。考虑一个 wiki 引擎。维基百科需要有一个每个人都可以看到的提示修订版，以及每个文档的回溯修订版。 wiki 还需要能够以与显示提示修订相同的方式显示向后修订。因此，两者应使用相同的存储格式。
我建议您允许对所有实体类型进行版本控制。当您编辑实体类型时，您将编辑尖端修订版本并存储包含先前值的后修订版本。 （编辑提示修订而不是插入新提示的原因是因为当前未具体化到 ObjectContext 中的其他对象可能包含指向提示的链接，您希望将其保留为指向提示的链接，而不是指向后修订版本。）
如有必要，您可以对 SQL Server 表进行分区，以便将后修订版本存储在不同的文件组中。这将允许您分别备份尖端修订和后修订。
Let's see. You have a requirement to take an object graph and serialize it into the database in a format which will allow you to materialize it later on. I think that there are tools which do exactly this. One of them, it strikes me, is the Entity Framework.
What you want to do is a very common thing. Consider a wiki engine. The wiki needs to have a tip revision that everyone sees, plus back revisions of every document. The wiki also needs to be able to display a back revision in just the same way that a tip revision is displayed. Therefore, the same storage format should be used for both of them.
I would propose that you allow all of your entity types to be versioned. When you edit an entity type, you will edit the tip revision and store a back revision containing the previous values. (The reason you edit the tip revision instead of inserting a new tip is because other objects, which are not currently materialized into an ObjectContext, may contain links to the tip which you would like to preserve as links to the tip, rather than links to the back revision.)
If necessary, you can partition your SQL Server tables so that the back revisions are stored in a different file group. This would allow you to backup the tip revisions and back revisions separately.
首先，您需要向表中添加一组属性：
然后，您有几个关于如何存储版本历史记录的选项。您可以
或者您可以序列化您的实体中所有有趣的内容，并将您想要版本化的所有实体的所有序列化 blob 存储在一个全局历史记录表中（我个人更喜欢第一种方法）。
您如何填写历史表格？通过更新和删除触发器。
请注意，越来越多的现代系统并没有真正删除任何内容。他们只是将内容标记为已删除。如果您想遵循此模式（这有几个好处）-而不是删除，请向您的实体添加 IsDeleted 标志（当然，您必须在各处过滤掉已删除的实体）。
你如何看待你的历史？只需使用历史表，因为它具有与主表相同的属性 - 应该不是问题。但是 - 扩展外键时 - 确保引用的实体版本与您存储在历史表中的版本相同。如果不是 - 您需要转到该引用实体的历史记录表并在那里获取值。这样，您将始终拥有实体在那一刻的样子的快照，包括所有引用。
除了上述之外 - 您还可以将实体的状态恢复到任何以前的版本。
请注意，此实现虽然简单，但可能会消耗一些空间，因为它存储快照，而不仅仅是所做的更改。如果您只想存储更改 - 在更新触发器中，您可以检测哪些字段已更改，将它们序列化并存储在全局历史表中。这样，您至少可以在用户界面中显示已更改的内容以及更改者（尽管您可能无法恢复到以前的版本）。
First you need to add a set of properties to your tables:
Then you have several options about how to store your version history. You can
Create a new table for each of the main tables you want to store history for. That history tables will have all the same fields as main table, but primary and foreign keys will not be enforced. For each foreign key also store Version of referenced entry at the time version was created.
OR you can serialize everything interesting about your entity and store all that serialized blobs for all entities you want to version in one global history table (I personally prefer first approach).
How do you fill your history tables? Via update and delete triggers.
Note that more and more modern systems do NOT really delete anything. They just mark things as deleted. If you would want to follow this pattern (which has several benefits) - instead of deleting add IsDeleted flag to your entities (of course you then have to filter deleted entities out everywhere).
How do you view your history? Just use history table, since it has all the same properties as main table - should not be a problem. But - when expanding foreign keys - ensure that referenced entity Version is the same as you store in your history table. If it's not - you need to go to History table of that referenced entity and grab values there. This way you will always have a snapshot of how entity looked like at THAT moment, including all references.
In addition to all above - you can also restore state of your entity to any previous version.
Note that this implementation, while easy, can consume some space, because it stores snapshot, not only changes being made. If you want to just store changes - in update trigger you can detect what fields has been changed, serialize them and store in global history table. That way you can at least show in user interface what has been changed and by whom (though you might have troubles to reverting to some previous version).
在我最近构建的一个项目中，我们插入了
DbContext类中的SaveChanges方法。这使我们能够访问 ChangeTracker 类的实例。调用ChangeTracker.Entries()可以让您访问DbEntityEntry列表。DbEntityEntry具有以下有趣的属性和方法：State- 是新创建、修改或删除的对象Entity- 对象的副本它代表CurrentValues- 已编辑值的枚举OriginalValues- 原始值的枚举我们为更改集和更改创建了一组 POCO，然后我们可以通过 EF 访问它们。这使我们的用户能够查看字段级别的更改以及日期和负责的用户。
On a project I recently built we used we plugged in to the
SaveChangesmethod in theDbContextclass. This gave us access to an instance of theChangeTrackerclass. CallingChangeTracker.Entries()gives you access to a list ofDbEntityEntry.DbEntityEntryhas the following interesting properties and methods:State- is the object newly created, modified or being deletedEntity- a copy of the object as it standsCurrentValues- an enumeration of the edited valuedOriginalValues- an enumeration of the original valuesWe created a set of POCOs for change sets and changes that we could then access through EF. This allowed our users to view field level changes along with dates and responsible users.
如果您使用的是 SQL Server 2016，请查看时态表（系统版本控制的时态表）
SQL Server 2016或Azure SQL。https:// learn.microsoft.com/en-us/sql/relational-databases/tables/temporal-tables?view=sql-server-ver15
来自文档：
我在此处编写了如何在不使用任何第三方库的情况下使用 Entity Framework Core 实现它的完整指南：
https://stackoverflow.com/a/64244548/3850405
Have a look at Temporal tables (system-versioned temporal tables) if you are using
SQL Server 2016< orAzure SQL.https://learn.microsoft.com/en-us/sql/relational-databases/tables/temporal-tables?view=sql-server-ver15
From documentation:
I wrote a complete guide how to implement it with Entity Framework Core without any third party libraries here:
https://stackoverflow.com/a/64244548/3850405