Many people have asked this question, but I am referring to something different. I have a table that stores facts. And this table contains the below columns.
CREATE TABLE [dbo].[XTABLE]
[identity] nvarchar(50) not null
[seq] int not null identity,
[valid_from] datetime2(0) not null,
[valid_to] datetime2(0) not null,
[user] nvarchar(50) not null,
constraint [pk] primary key ([seq])
create unique index [latest_by_identity] on [dbo].[XTABLE]([identity]) where [valid_to] = '9999-12-31'
Now imagine the below record:
ATXF8 | 1 | 2021-01-01 | 9999-12-31 | ....
And think about the scenario where I receive a new fact (record) with the same identity (ATXF8). What I need to do in the application is:
Fetch the last valid fact.
Update the [valid_to] field with the current date.
Create a new fact record and attach to DbContext.
Set the [valid_from] to be one second after the value [valid_to] from step 2.
Set the value of [valid_to] to be 9999-12-31.
Call the SaveChanges()
It turns out that the commands in the database can't be executed because EF Core is executing first the insert and because of the index I get an exception.
Is there anyway to control EF Core for this scenario to execute first the update and after the insert?
If not what other alternatives do I have? Create a transaction and call SaveChanges() twice?