Issue while saving using Entity Framework Core with datetime columns constraint

33 Views Asked by At

(this is another side, something similar but the others are difference to this question which was answered Issue while saving using Entity Framework Core with constraint)

I have a table UserSession with CreatedTime column of datetime not null type and LastUpdateTime column with datetime nullable type. In that table, I created a constraint on these 2 columns like this:

[CreatedTime] [DATETIME] NOT NULL DEFAULT GETUTCDATE(),
[LastUpdateTime] [DATETIME] NULL,
CONSTRAINT CK__UserSession__CreatedTime__LastUpdateTime CHECK
(
    [LastUpdateTime] IS NULL
    OR (
        [CreatedTime] <= [LastUpdateTime]
        AND [LastUpdateTime] <= GETUTCDATE() -- remove this line should run correctly
    )
)

When I try to update an existing entity to database like this

var session = await _dbContext.UserSessions.FindAsync(id);
session.LastUpdateTime = DateTime.UtcNow;
_dbContext.UserSessions.Add(newSession);
await _dbContext.SaveChangesAsync();

it throws an exception about the save progress was failed like this

Microsoft.EntityFrameworkCore.DbUpdateException : An error occurred while saving the entity changes. See the inner exception for details.

----> Microsoft.Data.SqlClient.SqlException : The UPDATE statement conflicted with the CHECK constraint "CK__UserSession__CreatedTime__LastUpdateTime". The conflict occurred in database "testdb", table "dbo.UserSession".

The statement has been terminated.

It looks like the saved entity is violating the constraint.

AND [LastUpdateTime] <= GETUTCDATE()

Business note: when an entity is created for the first time, the LastUpdateTime is null. after any column of this record is updated, the LastUpdateTime will be updated to utc time

So how can I resolve this saving data issue with an entity like this?

0

There are 0 best solutions below