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.

Solution 1:

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 FORSYSTEM_TIMEALLWHERE 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?

Solution 2:

Just to clarify, if you need aliases and FOR SYSTEM_TIME, use following syntax:

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

Solution 3:

SELECT name, compatibility_level FROM sys.databases;

It work only if your database is level 130 or more

Solution 4:

If u really want to use an alias, like if your query if quite complicated & u want better formatting, you can do this in 2 steps, using a CTE:

with _data as
(
    select*from Employee 
    forsystem_timeallwhere Id=15 
)

select*from _data as d

OR

with _data as
(select * 
    from Employee 
    for system_time all 
)

select *
from _data as d
where d.Id=15