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

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 ()
2023-001 - Update ()
2023-001 - Cancel ()
2023-002 - Create ()
2023-002 - Update ()
2023-002 - Cancel ()
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:

• It takes the first occurrence of each Year-Sequential key and process it.

• If it finds another time the same Year-Sequential key for another operation, it puts it aside for the next round of processing.

• When all the "not duplicated key" items have been processed, there's a call to dbContext.SaveChangesAsync().

• 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).

• 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 (Creat

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.

StackExchange Database Administrators Q#324433, answer score: 2