title: How can I use System-Versioned Temporal Table with Entity Framework?
body: <p>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 <a href="https://msdn.microsoft.com/en-us/library/mt591018.aspx?f=255&amp;MSPPError=-2147217396" rel="noreferrer">msdn</a>) with Entity Framework 6?</p>
  
  <p>I created a simple demo project with an employee temporal table:</p>
  
  <p><a href="https://i.stack.imgur.com/MO9Bg.png" rel="noreferrer"><img src="https://i.stack.imgur.com/MO9Bg.png" alt="enter image description here"></a></p>
  
  <p>I used the edmx to map the table to entity (<a href="https://stackoverflow.com/questions/40742142/entity-framework-not-working-with-temporal-table">thanks to Matt Ruwe</a>):</p>
  
  <p><a href="https://i.stack.imgur.com/DngNo.png" rel="noreferrer"><img src="https://i.stack.imgur.com/DngNo.png" alt="enter image description here"></a></p>
  
  <p>Everything works fine with pure sql statements:</p>
  
  <pre class="lang-cs prettyprint-override"><code>using (var context = new TemporalEntities())
  {
      var employee = context.Employees.Single(e =&gt; 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();
  }    
  </code></pre>
  
  <p>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 <code>IQuerable</code> isn't perfect. 
  Is there an existing extension or library to do this?</p>
  
  <p><strong>edit:</strong> (Based on the commentary by <a href="https://stackoverflow.com/users/1168070/pawel">Pawel</a>)</p>
  
  <p>I tried to use a Table-valued Function:</p>
  
  <pre><code>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
  );
  </code></pre>
  
  <pre class="lang-cs prettyprint-override"><code>using (var context = new TemporalEntities())
  {
      var employee = context.Employees.Single(e =&gt; e.EmployeeID == 2);
      var historyOfEmployee =
        context.GetEmployeeHistory(2, DateTime.MinValue, employee.ValidTo).ToList();
  } 
  </code></pre>
  
  <p>Do I have to create a function for each entity or is there a generic option?</p>