Querying Data in a System-Versioned Temporal Table in Entity Framework Core

Question

We are implementing a solution to query a temporal table.

When enabling a temporal table on SQL server for any table, SQL will automatically add a second table with extra “_History” at the end of the table to track history. For example, if we have a “student” table, SQL server will add “student_History” table.

To query the student history, all that we need is querying student table and add FOR SYSTEM_TIME AS OF '2015-09-01 T10:00:00.7230011'; at the end of the statement. So instead of write:

Select * from student

We will write:

Select * from student FOR SYSTEM_TIME AS OF '2015-09-01 T10:00:00.7230011'

Is there any way to automatically append this statement at the end of the query?

It is like intercepting the query and applying query filter like a soft table, but now it is not filtered, it is just statement at the end of the statement.

score 6 · Accepted Answer · answered Jun 27 '19 at 09:23

it could be done by an extension method, I found piece of code that may help you :

using Microsoft.EntityFrameworkCore;
using Microsoft.EntityFrameworkCore.Infrastructure;
using Microsoft.EntityFrameworkCore.Internal;
using Microsoft.EntityFrameworkCore.Migrations;
using System;
using System.Linq;

namespace core
{
    public static class Extensions
    {
        public static void AddTemporalTableSupport(this MigrationBuilder builder, string tableName, string historyTableSchema)
        {
            builder.Sql($@"ALTER TABLE {tableName} ADD 
                            SysStartTime datetime2(0) GENERATED ALWAYS AS ROW START HIDDEN NOT NULL,
                            SysEndTime datetime2(0) GENERATED ALWAYS AS ROW END HIDDEN NOT NULL,
                            PERIOD FOR SYSTEM_TIME (SysStartTime, SysEndTime);");
            builder.Sql($@"ALTER TABLE {tableName} SET (SYSTEM_VERSIONING = ON (HISTORY_TABLE = {historyTableSchema}.{tableName} ));");
        }

        public static DbContext GetDbContext<T>(this DbSet<T> dbSet) where T : class
        {
            var infrastructure = dbSet as IInfrastructure<IServiceProvider>;
            return (infrastructure.Instance.GetService(typeof(ICurrentDbContext)) as ICurrentDbContext).Context;
        }

        public static string GetTableName<T>(this DbSet<T> dbSet) where T : class
        {
            var entityType = dbSet.GetDbContext().Model.GetEntityTypes().FirstOrDefault(t => t.ClrType == typeof(T)) 
                ?? throw new ApplicationException($"Entity type {typeof(T).Name} not found in current database context!");
            var tableNameAnnotation = entityType.GetAnnotation("Relational:TableName");
            return tableNameAnnotation.Value.ToString();
        }

        public static IQueryable<T> ForSysTime<T>(this DbSet<T> dbSet, DateTime time) where T : class
        {
            return dbSet.FromSql($"SELECT * FROM dbo.[{dbSet.GetTableName()}] FOR SYSTEM_TIME AS OF {{0}}", time.ToUniversalTime());
        }


    }
}

Usage :

var date = DateTime.Parse("2018-08-28 16:30:00");
var students = ctx.student.ForSysTime(date);

this extension method was written by Mirek , you can find the complete article here.

hi, does this extension method work with wheres and groupsby, and other syntax with EF Linq? — , Jun 14 '20 at 22:45

score 3 · Answer 2 · edited Dec 01 '21 at 17:01

3

The latest version of Entity Framework Core (6) supports temporal tables.

As mentioned in here Microsoft devBlogs, EF Core 6.0 supports:

The creation of temporal tables using EF Core migrations
Transformation of existing tables into temporal tables, again using migrations
Restoring data from some point in the past
Querying historical data

Querying historical data can be seen here

edited Dec 01 '21 at 17:01

marc_s

732,580
175
1,330
1,459

answered Nov 10 '21 at 16:00

Falcon

41
3

score 0 · Answer 3 · answered Jan 26 '21 at 11:17

0

you may try this nuget pacakge https://www.nuget.org/packages/EntityFrameworkCore.SqlServer.TemporalTable/

It support:

create temporal query from Linq
migrations for temporal table.(work with Add-Migration, Script-Migration)

answered Jan 26 '21 at 11:17

terryfkjc

191
1
4

Querying Data in a System-Versioned Temporal Table in Entity Framework Core

3 Answers3