使用带有EF6的SQL 2016时态表，多对多关系 [英] Using SQL 2016 temporal tables with EF6, many to many relationships

问题描述

我们目前正在办公室开展一个项目，该项目利用SQL 2016的时态表 [ ^ ]功能以及Entity Framework 6 （EF6） 数据库优先 [ ^ ]模型。



时态表的一个要求是需要在表中添加两个特殊列，对应于记录的开始/结束时间。这两列是系统更新的datetime2列（类似于数字标识列），在正常情况下，任何更改数据的人都无法修改。



我们有几个通过多对多关系连接的表，以及它们之间的映射表，如下例所示（不能使用我们的真实结构，但这模型正是我们正在做的）：



学生< 1 ---- *>学生课程< * ---- 1>课程



StudentCourses表有一个主键，由两列组成，对应于学生和课程表的主键。如果这是一个普通的表，EF将生成两个不同的实体（学生和课程），并在它们之间添加一个关联属性，因此可以使用以下代码：

We are currently working on a project at the office that utilizes SQL 2016's Temporal Tables[^] functionality, along with Entity Framework 6 (EF6) with database-first[^] models.

One of the requirements of temporal tables is that there needs to be two "special" columns added to the table, corresponding to the record start/end times. These two columns are system-updated datetime2 columns (similarly to a numeric identity column), and cannot be modified by anyone changing the data under normal circumstances.

We have several tables that are connected through a many-to-many relationship, with an mapping table between them, as in the below example (can't use our real structure, but this models exactly what we are doing):

Students < 1 ---- * > StudentCourses < * ---- 1 > Courses

The StudentCourses table would have a primary key consisting of two columns, corresponding to the primary keys of both the Students and Courses tables. If this were a normal table, EF would generate two different entities (Student and Course), and add an association property between them, so the following code would be possible:

var courseList = myStudent.Courses();
var studentRoster = myCourse.Students();

不幸的是，由于额外的开始和结束时间必须存在于这些表中以暂时工作（这些不能被删除，因为这将是与医疗保健相关的应用程序，并且临时捕获用于审计和历史目的），创建映射实体是因为这些额外列被分类为映射的属性。代码必须以不同的方式编写（并且更复杂）：

Unfortunately, since the additional start and end times have to exist in these tables for temporal to work (these can't be removed as this will be a healthcare-related application and the temporal capture is being used for audit and historical purposes), a mapping entity is created because these extra columns are classified as attributes to the mapping. The code would have to be written differently (and more complex) as:

// This intermediate entity can become very confusing when
// there are multiple sets of these mappings,
// along with business logic to be able to identify
// the record(s) that are to be returned.

var courseList = myStudent.StudentCourses.Select(x => x.Course);
var studentRoster = myCourse.StudentCourses.Select(x => x.Student);

// This coding would also become a maintenance nightmare
// for future developers.

在正常情况下，这将是正确的行为。由于这些额外的列是系统维护的，不需要应用程序查看，也不是应用程序可编辑，它们实际上会导致不必要的行为。



我想要找到这个问题的解决方案，包括生成这些对象的* .tt文件的最小更改，或者可选地生成模型的不同方式，以便在对数据模型进行更改时重复它，因为这些不是由开发人员控制，并由另一个组维护（因此我们首先使用数据库，因此我们可以在数据库更改时快速轻松地重新生成）。



我尝试了什么：



分析* .tt文件以查看是否有可能更改模板的位置以生成对象不同，但没有找到可以修改的地方。



使用非时态表，问题不会发生。



标记列i n时间表隐藏没有区别。

Under normal circumstances, this would be the proper behavior. Since these additional columns are system-maintained, are not required to be seen by the application, nor are application editable, they are actually causing unwanted behavior.

I would like to find a solution to this issue involving minimal changes to either the *.tt files that generate these objects, or optionally a different way to generate the model so that it can be repeated as changes are made to the data model, as these are not controlled by the developer, and are maintained by another group (hence the reason we are using database first, so we can re-generate quickly and easily when the database changes).

What I have tried:

Analyzing the *.tt files to see if there is a place to possibly change the template to generate objects differently, but have not found where this could possibly be modified.

With non-temporal table, issue does not happen.

Marking columns in temporal table as hidden makes no difference.

推荐答案

EF6不支持时态表，因为EF Core 1不是EF Core 1.0（EF7）从头开始完全重写，使其与平台无关。这是一个当前的功能请求，正在进行评估，但不会很快就会显示出来。

Temporal tables are not supported by EF6 and is not in EF Core 1.0 (what was EF7) since EF Core 1 is a complete rewrite from the ground up to make it platform independent. It is a current feature request and is being evaluated, though don't hold out for it to show up any time soon.

这篇关于使用带有EF6的SQL 2016时态表，多对多关系的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！