In SQL Server Temporal Tables start/end date timestamps are identical for rows that are created inside a EF Core transaction

Question

I'm using a EF Core DbContext transaction to create/update entities on a SQL Server temporal table.

The data I'm processing (array of items) has a key (Year-Sequential, like: 2023-001, 2023-002, etc.) and then a set of attributes that corresponds to other columns on the table below. One of this attributes identifies the kind of operation: Create, Update, Cancel.

It may happen that I receive a request with many different operations related to a single Year-Sequential key, such as:

2023-001 - Create (<data>)
2023-001 - Update (<data>)
2023-001 - Cancel (<data>)
2023-002 - Create (<data>)
2023-002 - Update (<data>)
2023-002 - Cancel (<data>)
etc.

Since I'm using a temporal table to store this data to be able to track changes on the rows over time, I've coded the processing method like a single DB transaction with inside a recursive method that process subsets of the items. The recursive method works this way:

1. It takes the first occurrence of each Year-Sequential key and process it.
2. If it finds another time the same Year-Sequential key for another operation, it puts it aside for the next round of processing.
3. When all the "not duplicated key" items have been processed, there's a call to dbContext.SaveChangesAsync().
4. The processed entities are created and have the EntityId value assigned by the database (that I need to use for the next steps of processing).
5. If the list of "duplicated key items" is not empty, the method recursively calls itself on the duplicates list and starts again from step 1. If instead, the duplicated key items list is empty, the method ends and then there's a call to transaction.CommitAsync(), to commit the transaction and save everything to the database.

Everything works, but there's a problem with Start/End date used for temporal table period.

For each round of the recursive method I correctly get a row created on each single Year-Sequential key. So, taking previous data example, on the History table I've 2 old rows about 2023-001 (Create, 1st Update) with the same EntityId and, on the Current table, I've the third (2nd Update) row, so the one related to the last 2023-001 Cancel operation.

See the queries screenshot below.

The problem is that if I make a temporal query on that data ([MovimentiDati] table), using the FOR SYSTEM_TIME ALL clause, I get back only the current row! No way I can get the old rows too, but they exists! So, the history is all there but I can't get it!

Instead, if I query the History table ([MovimentiDatiHistory]) I correctly see the old rows with the same EntityId.

On the screenshot, you see 5 items with the same issue. For example, take the row with MovimentoId = 399, you can see:

• on MovimentiDatiHistory table: Creation of the row (1st run of recursive method and 1st call to dbcontext.SaveChangesAsync())
• on MovimentiDatiHistory table: First update on the row (2nd run of recursive method and 2nd call to dbcontext.SaveChangesAsync())
• on MovimentiDati (current) table: Second (and last) update on the row (3rd run of recursive method and 3rd call to dbcontext.SaveChangesAsync())

But, as highlighted in red, you can also see that Start/End date timestamps are identical! And I think this is the reason why I cannot get the correct result when I query the data from [MovimentiDati] table (current) with the temporal clause.

I'm not sure about where the problem is, if in the EF Core library or in SQL Server, but I would like to elaborate on the matter...

Answer 1

Temporal Tables start/end date timestamps are identical for rows that are created inside a … transaction

This is by design and is a SQL Server thing, not EF specific.

All start times for rows in temporal tables will be the same in a given transaction, the time of the start of the transaction. This makes sense from the point of view of transactions being atomic, there would either be ambiguity or excessive locking between overlapping transactions otherwise.¹

If you modify a row multiple times in the same transaction each version therefore has the same start time so those preceding in the same transaction have the property starttime==endtime.

I get back only the current row! No way I can get the old rows too, but they exists! So, the history is all there but I can't get it!

Again this is by design. I'm not entirely sure I agree with this particular design decision but it is what it is! It only affects the standard querying syntax though, as you've identified the intermediate rows do exist so you can read them by querying the history table (union-all-ed with the base table) more manually. If you care about the order of the updates you will need to include your own revision counter in the table and modify it on each change, otherwise you can't tell which revisions came when within the set.

---

^{[1] For this reason I expect to see similar behaviour in implementations of the feature in other DBs², not just SQL Server
[2] In fact I'd not be surprised if it isn't in the standard³ given system-versioned tables are sometimes called “transaction time tables”
[3] system-versioned tables and related features were introduced in SQL:2011}

Answer 2

It seems that it is a SQL Server by design behavior. Taken from Microsoft Temporal tables documentation:

FOR SYSTEM_TIME filters out rows that have a period of validity with zero duration (ValidFrom = ValidTo).

Those rows will be generated if you perform multiple updates on the same primary key within the same transaction. In that case, temporal querying returns only row versions before the transactions, and current rows after the transactions.

If you need to include those rows in the analysis, query the history table directly.

Please also take a look at this Issue opened on EF Core GitHub repo.