TSR TSR - 4 months ago 20
C# Question

C# SqlConnection Querying Temporal tables

I have a temporal table

Employee
with
EmployeeHistory
as its history table.

In C#, I am using
SqlConnection
to query the data from SQL Server for the entire history of an employee.

var data = Conn.ExecuteReader("select * from Employee e FOR SYSTEM_TIME ALL WHERE e.Id=15");


This throws the error:


Incorrect syntax near FOR


So, how do we query history data for a temporal table in C# using
SqlConnection
?

Answer Source

Problem is you are using table alias e and so the error. Don't think you can use table alias. change it to

select * from Employee FOR SYSTEM_TIME ALL WHERE Id=15 

If you check the documentation Querying Data in a System-Versioned Temporal Table (OR) Temporal Tables you will see that the syntax doesn't show the use of table alias at all. Rather you will have to use the entire table name.

See this related post as well Why does the FOR Clause not work with an alias in SQL Server 2016 with temporal tables?