Microsoft SQL 2016+ Temporal Table Bug
Submitted this as a bug via LINQPad, but wanted to put this out in case anyone else runs across this:
I have a single temporal table in MS SQL 2017 with one entry I have modified two times.
Running the query in MS SQL Management Studio, note SysEndTime/SysStartTime are the fields SQL auto updates:
SELECT [all of the fields I care about] [sd].[SysEndTime], [sd].[SysStartTime]
FROM (
SELECT * FROM dbo.TemporalTableIMadeUp FOR SYSTEM_TIME ALL
) AS [sd]
WHERE ([sd].[Id] = GuidICareAbout)
It will return three entries, the 'live' entry that lives in the normal table, and the two 'historical' entries that live in the history table.
Running the following linq from asp.net core returns the same results:
var result = TemporalTableIMadeUp.FromSql("SELECT * FROM dbo.TemporalTableIMadeUp FOR SYSTEM_TIME ALL").Where(xx => xx.Id == GuidICareAbout).ToList()
Running the same linq statement from LINQPad v5.36.03 returns three statements, however, they are only the elements from the 'live' table and do not include the elements from the historical table. i.e. the 'live' table result is repeated three times.
Taking the SQL generated in LINQPad and running it in Management Studio returns the proper result...so the query is properly generated, not sure if this is a display issue or not.
I have a single temporal table in MS SQL 2017 with one entry I have modified two times.
Running the query in MS SQL Management Studio, note SysEndTime/SysStartTime are the fields SQL auto updates:
SELECT [all of the fields I care about] [sd].[SysEndTime], [sd].[SysStartTime]
FROM (
SELECT * FROM dbo.TemporalTableIMadeUp FOR SYSTEM_TIME ALL
) AS [sd]
WHERE ([sd].[Id] = GuidICareAbout)
It will return three entries, the 'live' entry that lives in the normal table, and the two 'historical' entries that live in the history table.
Running the following linq from asp.net core returns the same results:
var result = TemporalTableIMadeUp.FromSql("SELECT * FROM dbo.TemporalTableIMadeUp FOR SYSTEM_TIME ALL").Where(xx => xx.Id == GuidICareAbout).ToList()
Running the same linq statement from LINQPad v5.36.03 returns three statements, however, they are only the elements from the 'live' table and do not include the elements from the historical table. i.e. the 'live' table result is repeated three times.
Taking the SQL generated in LINQPad and running it in Management Studio returns the proper result...so the query is properly generated, not sure if this is a display issue or not.