EntityFrameworkCore.SqlServer.TemporalTable 1.2.2

Setup

1. Add the following class in Startup project.

class EnhancedDesignTimeServices : IDesignTimeServices
{
    public void ConfigureDesignTimeServices(IServiceCollection services)
    {
        services.AddSingleton<ICSharpMigrationOperationGenerator, TemporalCSharpMigrationOperationGenerator>();
    }
}

2. Use the following method to configure DbContext. UseInternalServiceProvider need to be called to let DbContext resolve depencdencies with the provider we have configure.

IServiceCollection services = new ServiceCollection();

services.AddDbContext<TemporalTestDbContext>((provider, options) =>
{
      options.UseSqlServer(configuration.GetConnectionString("DefaultConnection"));
      options.UseInternalServiceProvider(provider);
});

services.AddEntityFrameworkSqlServer();
services.RegisterTemporalTablesForDatabase();
var provider = services.BuildServiceProvider();

return provider.GetService<TemporalTestDbContext>();

Entity Configuration

1. Enable temporal table for entity

modelBuilder.Entity<User>(b =>
{
      b.ToTable("Users");
      b.HasTemporalTable();
});

2. Enable temporal table with custom start/end date column name

modelBuilder.Entity<TransactionRecord>(b =>
{
          b.HasTemporalTable(config =>
          {
              config.StartDateColumn("ValidFrom");
              config.EndDateColumn("NewValidTo");
          });
});

3. Optional. You may configure the history table name

modelBuilder.Entity<User>(b =>
{
      b.ToTable("Users");
      b.HasTemporalTable(config =>
      {
            config.HistorySchema("history");
            config.HistoryTable("UserHistories");
      });
});

Temporal Query

Example:

modelBuilder.Entity<TransactionRecord>(b =>
{
        b.HasTemporalTable(config =>
        {
                config.StartDateColumn("ValidFrom");
                config.EndDateColumn("NewValidTo");
                config.DataConsistencyCheck(true);
        });
});

var transactions = context.Set<TransactionRecord>()
                           .FromTo(_InititalDate, _IncrementedDate)
                           .Select(t => new
                           {
                               Amount = t.Amount,
                               CreatedDate = t.CreatedDate,
                               SysStartDate = EF.Property<DateTime>(t, TemporalAnnotationNames.DefaultStartTime),
                               SysEndDate = EF.Property<DateTime>(t, TemporalAnnotationNames.DefaultEndTime)
                           });

Generated SQL:

DECLARE __p_0 datetime2 = '2021-01-26T11:04:36.3259831Z';
DECLARE __p_1 datetime2 = '2021-01-26T11:04:41.5200058Z';

SELECT [t].[Amount], [t].[CreatedDate], [t].[ValidFrom] AS [SysStartDate], [t].[NewValidTo] AS [SysEndDate]
FROM [TransactionRecord] FOR SYSTEM_TIME FROM @__p_0 TO @__p_1 AS [t]

This extention will create two shadow properties for the Start/End date column. The property name is defined in TemporalAnnotationNames class.

Supported Temporal Queries:

• AS OF <date_time>
• FROM <start_date_time> TO <end_date_time>
• BETWEEN <start_date_time> AND <end_date_time>
• CONTAINED IN (<start_date_time> , <end_date_time>)
• ALL

Migrations

Here are the migrations generated by EntityFramework migration tool (Add-Migration, Script-Migration)

Supported migration operations

• Adding a new table with history tracking.
• Enable history tracking on existing table.
• Disable history tracking on existing temporal table.
• Support custom column name for "start/end" date column.
• Support custom schema and table name for history table.
• Support "start/end" date column rename.
• Support option for data consistency check.

Sample Migrations Generated by EntityFramework Tool

1. Adding a new table with temporal feature.

protected override void Up(MigrationBuilder migrationBuilder)
{
        migrationBuilder.CreateTable(
                name: "TransactionRecord",
                columns: table => new
                {
                    Id = table.Column<int>(type: "int", nullable: false)
                        .Annotation("SqlServer:Identity", "1, 1"),
                    Amount = table.Column<decimal>(type: "decimal(18,2)", nullable: false),
                    CreatedDate = table.Column<DateTime>(type: "datetime2", nullable: false),
                    LastModifiedDate = table.Column<DateTime>(type: "datetime2", nullable: false),
                    ValidTo = table.Column<DateTime>(type: "datetime2", nullable: false),
                    ValidFrom = table.Column<DateTime>(type: "datetime2", nullable: false)
                },
                constraints: table =>
                {
                    table.PrimaryKey("PK_TransactionRecord", x => x.Id);
                });

        migrationBuilder.EnableTemporalTable(
                table: "TransactionRecord",
                historyTable: "TransactionRecordHistories",
                startColumn: "ValidFrom",
                endColumn: "ValidTo");
}

2. Update existing table from non-temporal become temporal table

migrationBuilder.AddColumn<DateTime>(
                name: "SysEndTime",
                table: "Users",
                type: "datetime2",
                nullable: false,
                defaultValue: new DateTime(1, 1, 1, 0, 0, 0, 0, DateTimeKind.Unspecified));

migrationBuilder.AddColumn<DateTime>(
                name: "SysStartTime",
                table: "Users",
                type: "datetime2",
                nullable: false,
                defaultValue: new DateTime(1, 1, 1, 0, 0, 0, 0, DateTimeKind.Unspecified));

migrationBuilder.EnableTemporalTable(
                table: "Users",
                historyTable: "UsersHistories",
                startColumn: "SysStartTime",
                endColumn: "SysEndTime");

3. Disable history tracking

migrationBuilder.DisableTemporalTable(
                table: "Users");

migrationBuilder.DropColumn(
                name: "DateFrom",
                table: "Users");

migrationBuilder.DropColumn(
                name: "SysEndTime",
                table: "Users");

Please note that both the SysStart and SysEnd date column will be dropped by default.