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?

Solution 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

Solution 2

No, I am afraid, you cannot. I have been back & forth with Microsoft gurus on this front.

This is a known issue. And the best advice I have found is to use FromSql as explained here.

Solution 3

Yes you can with a little effort ...

Intercepting EFF intents when trying insert or update generated always columns and avoid errors like

"Cannot insert an explicit value into a GENERATED ALWAYS column in table 'xxx.dbo.xxxx'. Use INSERT with a column list to exclude the GENERATED ALWAYS column, or insert a DEFAULT into GENERATED ALWAYS column."

After that it's work like a charm (already in production on Azure Db)

Example of implementation EFF6 based on columns (StartTime y EndTime) based on:

entity-framework-not-working-with-temporal-table

insert-record-in-temporal-table-using-c-sharp-entity-framework

dbset-attachentity-vs-dbcontext-entryentity-state-entitystate-modified

Thank you!

using System.Data.Entity.Infrastructure.Interception;
using System.Data.Entity.Core.Common.CommandTrees;
using System.Data.Entity.Core.Metadata.Edm;
using System.Collections.ObjectModel;
using System.Collections.Generic;
using System.Linq;
using System.Data.Entity;
namespace Ubiquité.Clases
{
    /// <summary>
    /// Evita que EFF se haga cargo de ciertos campos que no debe tocar Ej: StartTime y EndTime
    ///     de las tablas versionadas o bien los row_version por ejemplo
    ///     https://stackoverflow.com/questions/40742142/entity-framework-not-working-with-temporal-table
    ///     https://stackoverflow.com/questions/44253965/insert-record-in-temporal-table-using-c-sharp-entity-framework
    ///     https://stackoverflow.com/questions/30987806/dbset-attachentity-vs-dbcontext-entryentity-state-entitystate-modified
    /// </summary>
    /// <remarks>
    /// "Cannot insert an explicit value into a GENERATED ALWAYS column in table 'xxx.dbo.xxxx'.
    /// Use INSERT with a column list to exclude the GENERATED ALWAYS column, or insert a DEFAULT
    /// into GENERATED ALWAYS column."
    /// </remarks>
    internal class TemporalTableCommandTreeInterceptor : IDbCommandTreeInterceptor
    {
        private static readonly List<string> _namesToIgnore = new List<string> { "StartTime", "EndTime" };
        public void TreeCreated(DbCommandTreeInterceptionContext interceptionContext)
        {
            if (interceptionContext.OriginalResult.DataSpace == DataSpace.SSpace)
            {
                var insertCommand = interceptionContext.Result as DbInsertCommandTree;
                if (insertCommand != null)
                {
                    var newSetClauses = GenerateSetClauses(insertCommand.SetClauses);
                    var newCommand = new DbInsertCommandTree(
                        insertCommand.MetadataWorkspace,
                        insertCommand.DataSpace,
                        insertCommand.Target,
                        newSetClauses,
                        insertCommand.Returning);
                    interceptionContext.Result = newCommand;
                }
                var updateCommand = interceptionContext.Result as DbUpdateCommandTree;
                if (updateCommand != null)
                {
                    var newSetClauses = GenerateSetClauses(updateCommand.SetClauses);
                    var newCommand = new DbUpdateCommandTree(
                        updateCommand.MetadataWorkspace,
                        updateCommand.DataSpace,
                        updateCommand.Target,
                        updateCommand.Predicate,
                        newSetClauses,
                        updateCommand.Returning);
                    interceptionContext.Result = newCommand;
                }
            }
        }
        private static ReadOnlyCollection<DbModificationClause> GenerateSetClauses(IList<DbModificationClause> modificationClauses)
        {
            var props = new List<DbModificationClause>(modificationClauses);
            props = props.Where(_ => !_namesToIgnore.Contains((((_ as DbSetClause)?.Property as DbPropertyExpression)?.Property as EdmProperty)?.Name)).ToList();
            var newSetClauses = new ReadOnlyCollection<DbModificationClause>(props);
            return newSetClauses;
        }
    }
    /// <summary>
    /// registra TemporalTableCommandTreeInterceptor con EFF
    /// </summary>
    public class MyDBConfiguration : DbConfiguration
    {
        public MyDBConfiguration()
        {
            DbInterception.Add(new TemporalTableCommandTreeInterceptor());
        }
    }
}

Comments

[Pawel]

Have you considered using a TVF?

[cSteusloff]

It is possible to get the correct entity type? In this example that GetEmployeeHistory retruns Employee instead of "unknown" type?

[Pawel]

Yes. It is possible to get the right entity type. I don't remember from the top of my head but the designer may want to add a type to the model for your TVF. You don't want that - you want to use an existing type. The important thing is that TVFs returns the right columns so that it is possible to materialize entities.

[cSteusloff]

Little update, if you use EFCore: nuget.org/packages/EFCoreTemporalSupport