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.

Answer 1

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

Answer 2

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.