Support for temporal tables

[FaizulHussain]

Properties for period columns in a temporal table is generated when it's hidden

CREATE TABLE Department
(
    DeptID INT NOT NULL PRIMARY KEY CLUSTERED
  , DeptName VARCHAR(50) NOT NULL
  , ManagerID INT NULL
  , ParentDeptID INT NULL
  , _SysStartTime_ DATETIME2 GENERATED ALWAYS AS ROW START **HIDDEN** NOT NULL
  , _SysEndTime_ DATETIME2 GENERATED ALWAYS AS ROW END **HIDDEN** NOT NULL
  , PERIOD FOR SYSTEM_TIME (SysStartTime, SysEndTime)
)
WITH (SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo._DepartmentHistory_));

Also classes are generated for History table (but I guess can exclude it with teh exclusion filter for now)

[sjh37]

Thanks @FaizulHussain I shall investigate.

[matt-neubert]

A couple of things to note on this. I was able to get it to work by hiding my Row Start field. The issue that came up for me is that default on row start would get mapped into the constructor. This was fine if the row already existed because I did not change this field when saving therefore there was no change being persisted (which would fail because Generated Always). This issue is when creating a new entity and it got the default DateTime.Now and tried to save the Row Start field.

It would be great if we could include these fields for read but if always generated they wouldn't be tracked or something.

[sjh37]

The above SQL would generate the following C#

// TemporalDepartment
public class TemporalDepartment
{
    public int DeptId { get; set; } // DeptID (Primary key)
    public string DeptName { get; set; } // DeptName (length: 50)
    public int? ManagerId { get; set; } // ManagerID
    public int? ParentDeptId { get; set; } // ParentDeptID
    public DateTime SysStartTime { get; set; } // SysStartTime
    public DateTime SysEndTime { get; set; } // SysEndTime
}

// TemporalDepartmentHistory
public class TemporalDepartmentHistory
{
    public int DeptId { get; set; } // DeptID (Primary key)
    public string DeptName { get; set; } // DeptName (Primary key) (length: 50)
    public int? ManagerId { get; set; } // ManagerID
    public int? ParentDeptId { get; set; } // ParentDeptID
    public DateTime SysStartTime { get; set; } // SysStartTime (Primary key)
    public DateTime SysEndTime { get; set; } // SysEndTime (Primary key)
}

// TemporalDepartment
public class TemporalDepartmentConfiguration : IEntityTypeConfiguration<TemporalDepartment>
{
    public void Configure(EntityTypeBuilder<TemporalDepartment> builder)
    {
        builder.ToTable("TemporalDepartment", "dbo");
        builder.HasKey(x => x.DeptId).HasName("PK__Temporal__0148818EB27A7514").IsClustered();

        builder.Property(x => x.DeptId).HasColumnName(@"DeptID").HasColumnType("int").IsRequired().ValueGeneratedNever();
        builder.Property(x => x.DeptName).HasColumnName(@"DeptName").HasColumnType("varchar(50)").IsRequired().IsUnicode(false).HasMaxLength(50);
        builder.Property(x => x.ManagerId).HasColumnName(@"ManagerID").HasColumnType("int").IsRequired(false);
        builder.Property(x => x.ParentDeptId).HasColumnName(@"ParentDeptID").HasColumnType("int").IsRequired(false);
        builder.Property(x => x.SysStartTime).HasColumnName(@"SysStartTime").HasColumnType("datetime2").IsRequired().ValueGeneratedOnAdd();
        builder.Property(x => x.SysEndTime).HasColumnName(@"SysEndTime").HasColumnType("datetime2").IsRequired().ValueGeneratedOnAdd();
    }
}

// TemporalDepartmentHistory
public class TemporalDepartmentHistoryConfiguration : IEntityTypeConfiguration<TemporalDepartmentHistory>
{
    public void Configure(EntityTypeBuilder<TemporalDepartmentHistory> builder)
    {
        builder.ToTable("TemporalDepartmentHistory", "dbo");
        builder.HasKey(x => new { x.DeptId, x.DeptName, x.SysStartTime, x.SysEndTime });

        builder.Property(x => x.DeptId).HasColumnName(@"DeptID").HasColumnType("int").IsRequired().ValueGeneratedNever();
        builder.Property(x => x.DeptName).HasColumnName(@"DeptName").HasColumnType("varchar(50)").IsRequired().IsUnicode(false).HasMaxLength(50).ValueGeneratedNever();
        builder.Property(x => x.ManagerId).HasColumnName(@"ManagerID").HasColumnType("int").IsRequired(false);
        builder.Property(x => x.ParentDeptId).HasColumnName(@"ParentDeptID").HasColumnType("int").IsRequired(false);
        builder.Property(x => x.SysStartTime).HasColumnName(@"SysStartTime").HasColumnType("datetime2").IsRequired().ValueGeneratedNever();
        builder.Property(x => x.SysEndTime).HasColumnName(@"SysEndTime").HasColumnType("datetime2").IsRequired().ValueGeneratedNever();

        builder.HasIndex(x => new { x.SysEndTime, x.SysStartTime }).HasDatabaseName("ix_TemporalDepartmentHistory");
    }
}

[sjh37]

Because the TemporalDepartmentConfiguration contains this:

builder.Property(x => x.SysStartTime)...ValueGeneratedOnAdd();
builder.Property(x => x.SysEndTime)...ValueGeneratedOnAdd();

and TemporalDepartmentHistoryConfiguration contains this:

builder.Property(x => x.SysStartTime)...ValueGeneratedNever();
builder.Property(x => x.SysEndTime)...ValueGeneratedNever();

@FaizulHussain Is there anything else required?

@matt-neubert Could you supply my with the SQL for your temporal table as I don't have any defaults set.

[sjh37]

This has been released in v3.8.2. Any problems, please let me know.