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?
