question

GeorgeParissis-9878 avatar image
0 Votes"
GeorgeParissis-9878 asked DanielZhang-MSFT commented

Order of SQL CRUD operations in Entity Framework Core

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])
)

go

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:

  1. Fetch the last valid fact.

  2. Update the [valid_to] field with the current date.

  3. Create a new fact record and attach to DbContext.

  4. Set the [valid_from] to be one second after the value [valid_to] from step 2.

  5. Set the value of [valid_to] to be 9999-12-31.

  6. 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?

Thank you,
George

dotnet-entity-framework-core
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

1 Answer

DanielZhang-MSFT avatar image
0 Votes"
DanielZhang-MSFT answered DanielZhang-MSFT commented

Hi GeorgeParissis-9878,
Did you change the state of the entity after you attached it?
Please refer to this thread.
And double check setting updating the status as Added Or Modified.
You can set the EntityState of the newly attached entity to Modified manually.

 context.Entry(yourEntity).State = EntityState.Modified;

More details please refer to this document.
If not successful, please provide some related code.
Best Regards,
Daniel Zhang


If the response is helpful, please click "Accept Answer" and upvote it.

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.


· 2
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hi Daniel,

All the above steps are executed in the same context. The loading of the first record, the update of the valid_to field and the creation of a new record.

So normally the dbcontext would modify the state of the first record to modified after the value of the property changes.

When I remove the unique constraint on the index then update and create are executed. That for me is another indicator that the dbcontext indeed has the correct states for both records.

Based on your comment are you saying that EF should start executing first the updates and after the creates and that is why you focused on the State property?

Thank you,
George

0 Votes 0 ·

Hi @GeorgeParissis-9878,
First of all, I am sorry that misunderstood your previous question a bit.
For most database providers, SaveChanges is transactional. This means all the operations will either succeed or fail and the operations will never be left partially applied.
When SaveChanges() is executed to insert, update or delete on the database then Entity framework will wrap that operation in a transaction.
So if you want to update first and then create, you can call SaveChanges after updating, and then call again after creating.
More details please refer to this thread.
Best Regards,
Daniel Zhang


0 Votes 0 ·