Using SQL 2016 temporal tables with EF6, many to many relationships

See more:

C#

SQL-Server

entity-framework

, +

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.

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.