如何在实体框架中使用系统版本的临时表？ [英] How can I use System-Versioned Temporal Table with Entity Framework?

问题描述

我可以在SQL Server 2016中使用时态表。不幸的是，Entity Framework 6尚不知道此功能。是否有使用新查询选项的变通办法（请参阅

我使用edmx将表格映射到实体（

使用纯sql语句，一切都可以正常工作：

 使用（var context = new Temporal Entities（））
 {
 var employee = context.Employees.Single（e => e.EmployeeID == 2）; 
 var query = 
 $ @ SELECT * FROM [TemporalTest]。[dbo]。[{nameof（Employee）}] 
 FOR SYSTEM_TIMEbetween 
'0001-01-01 00：00：00.00'和'{employee.ValidTo：O}'
 WHERE EmployeeID = 2; 
 var historyOfEmployee = context.Employees.SqlQuery（query）.ToList（）; 
} 
  

是否可以在没有纯SQL的情况下向每个实体添加历史记录功能？我的实体扩展解决方案具有反射功能，可以通过 IQuerable 操纵SQL查询，这不是很完美。
是否有现成的扩展程序或库可以这样做？

编辑：（基于Pawel ）

我尝试使用表值函数：

 创建功能dbo.GetEmployeeHistory（
 @EmployeeID int，
 @startTime datetime2，
 @endTime datetime2） 
收益表
作为
收益
（
选择
 EmployeeID，
 [Name]，
职位，
部门，
 [地址]，
 ValidFrom，
 ValidTo 
来自dbo.Employee 
 FOR SYSTEM_TIME BETWEEN @startTime和@endTime 
 WHERE EmployeeID = @EmployeeID 
）; 
  

 使用（var context = new TemporalEntities（））
 {
 var employee = context.Employees.Single（e => e.EmployeeID == 2）; 
 var historyOfEmployee = 
 context.GetEmployeeHistory（2，DateTime.MinValue，employee.ValidTo）.ToList（）; 
} 
  

我必须为每个实体创建一个函数还是有一个通用选项？

解决方案

不，恐怕您不能。我已经回来了在此方面带有 Microsoft大师。

这是已知的问题。
我发现的最佳建议是使用 FromSql ，如此处。

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?

解决方案

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.

这篇关于如何在实体框架中使用系统版本的临时表？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！