C# SqlConnection Querying Temporal tables

Question

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?

Squeaky

Can you update your post with a screenshot of the exception occurring? Also, please include the source code for the declaration and instantiation of the Conn variable. – Ermaermanno

Can you execute such command using SQL Server? – Guru

What version of SQL Server are you connecting to? – Geffner

As I realized, this query doens't work in SQL Server too, when given alias to the Employee table, but works without the alias, strange. i.e. "select * from Employee FOR SYSTEM_TIME ALL WHERE Id=15 works. – Squeaky

don't think you can use table alias. change it to select * from Employee FOR SYSTEM_TIME ALL WHERE Id=15 – Tommy

According to this documentaiton SQL Server 2017 is where support for temporal tables was added learn.microsoft.com/en-us/sql/relational-databases/tables/… – Corwin

@Tommy So, how to make it work with the alias, I am using a much complicated query where I need the alias. – Squeaky

SqlConnection is only for establishing the connection to the database - in order to execute a query, you must use SqlCommand ...... – Signalment

@juharr: that page clearly states: SQL Server (starting with 2016) - temporal tables were in fact introduced in SQL Server 2016 - not 2017 – Signalment

@Signalment I see what you mean, I just went off the first sentence SQL Server 2017 introduces support for system-versioned temporal tables – Corwin

@Signalment I think the OP may be using Dapper which includes a ExecuteReader extension on the IDbConnection interface. – Corwin

@juharr: good point - would be helpful if OP would mention something like that! – Signalment

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

Tommy

So, how to make it work with the alias, I am using a much complicated query where I need the alias. – Squeaky

Why do you need it (the alias) @Squeaky ? – Ermaermanno

So, it works, when I remove the alias, instead using the tablename.columnname to reference it's columns. Strange thing. – Squeaky

Alias is used just make things easy and readable, instead of using the table name. – Squeaky

Alas you can't use them in this scenario @Squeaky . – Ermaermanno

The alias is just in the wrong place: select * from Employee FOR SYSTEM_TIME ALL e WHERE e.Id=15 should work – Terriss

Answer 2

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

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

Sophisticated

Why is this answer an improvement over the already accepted one? – Hillier

Approved anwser says that you cannot use FOR SYSTEM_TIME and aliases in one statement. My anser clarifies that it is possible – Sophisticated

Great, you should edit your answer to clarify that :) – Hillier

Answer 3

SELECT name, compatibility_level FROM sys.databases;

It work only if your database is level 130 or more

Deer

Answer 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 
    for system_time all 
    where 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 

Quart