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

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.