How can I use System-Versioned Temporal Table with Entity Framework?

Question

I can use temporal tables in SQL Server 2016. Entity Framework 6 unfortunately does not know this feature yet. Is there the possibility of a workaround to use the new querying options (see msdn) with Entity Framework 6?

I created a simple demo project with an employee temporal table:

I used the edmx to map the table to entity (thanks to Matt Ruwe):

Everything works fine with pure sql statements:

using (var context = new TemporalEntities())
{
    var employee = context.Employees.Single(e => e.EmployeeID == 2);
    var query = 
      $@"SELECT * FROM [TemporalTest].[dbo].[{nameof(Employee)}]
         FOR SYSTEM_TIME BETWEEN
         '0001-01-01 00:00:00.00' AND '{employee.ValidTo:O}'
         WHERE EmployeeID = 2";
    var historyOfEmployee = context.Employees.SqlQuery(query).ToList();
}    

Is it possible to add the history functionality to every entity without pure SQL? My solution as entity extension with reflection to manipulate the SQL query from IQuerable isn't perfect. Is there an existing extension or library to do this?

edit: (Based on the commentary by Pawel)

I tried to use a Table-valued Function:

CREATE FUNCTION dbo.GetEmployeeHistory(
    @EmployeeID int, 
    @startTime datetime2, 
    @endTime datetime2)
RETURNS TABLE
AS
RETURN 
(
    SELECT 
        EmployeeID,
        [Name], 
        Position, 
        Department, 
        [Address],
        ValidFrom,
        ValidTo
    FROM dbo.Employee
    FOR SYSTEM_TIME BETWEEN @startTime AND @endTime
    WHERE EmployeeID = @EmployeeID
);

using (var context = new TemporalEntities())
{
    var employee = context.Employees.Single(e => e.EmployeeID == 2);
    var historyOfEmployee =
      context.GetEmployeeHistory(2, DateTime.MinValue, employee.ValidTo).ToList();
} 

Do I have to create a function for each entity or is there a generic option?

Answer 1

Initial support for Temporal Tables has been added here: e7c0b9d (model/metadata part) and here 4b25a88 (query part) and will be available in the next preview (Preview 8), as well as in current nightly bits.

Usage:

Mapping entity to a temporal table can be done in the OnModelCreating, like this:

modelBuilder.Entity<MyTemporalEntity>().ToTable(tb => tb.IsTemporal());

additional configuration is also supported - history table name/schema, names for period start and period end columns

modelBuilder.Entity<MyTemporalEntity>().ToTable(tb => tb.IsTemporal(ttb =>
{
    ttb.HasPeriodStart("SystemTimeStart");
    ttb.HasPeriodEnd("SystemTimeEnd");
    ttb.WithHistoryTable("MyHistoryTable", "mySchema");
}));

Migrations are supported so existing entities can be converted to temporal.

Querying:

var myDate = new DateTime(2020, 1, 1);
context.MyTemporalEntities.TemporalAsOf(myDate).Where(e => e.Id < 10);

Supported operations: TemporalAsOf, TemporalAll, TemporalBetween, TemporalFromTo, TemporalContainedIn.

Some limitations and considerations

• Queries that use temporal operations are always marked as ``NoTracking`. Multiple entities with the same key could be returned from such queries and EF would not be able to resolve their identities properly otherwise.

• Temporal operations are supported directly on DbSet, rather than IQueryable. In case of inheritance, they can't be applied on OfType operation. Instead, use:

context.Set<MyDerivedEntity>().TemporalAsOf(...);

• Navigation expansion is only supported for AsOf operation, since it's the only temporal operation that guarantees consistency of the result graph. For other temporal operations navigations must be created manually using Join.

• When expanding navigation, the target entity must also be mapped to temporal table. Temporal operation gets propagated from source to the target. Navigating from temporal to non-temporal entity is not supported.

context.Customers.TemporalAsOf(new DateTime(2020, 1, 1)).Select(c => c.Orders)

will return customers and their orders as of Jan 1st 2020. Temporal operation gets applied to customers AND orders automatically.

• Set operations (e.g. Concat, Except) on arguments mapped to temporal tables are not supported. (issue tracked here #25365)

Quote from maumar