CODEWITHSUNDEEP
c#entity-frameworkef-fluent-api
Learner
Learner

Reputation: 55

Compute field on update and Insert using Fluent API

I am trying to configure a field in model which is to be modified with insert and update.

I tried multiple things but still could find the correct configuration for my requirement.

Example :

public class Author
{
    public DateTime UpdatedAt { get; set; } 
}

I have field UpdatedAt which is to be computed on the SQL Server side when inserting/updating a row.

So far I have tried following

  1. I tried data annotations like this but didn't meet the requirement.

     public class Author
 {
     [DatabaseGenerated(DatabaseGeneratedOption.Computed)]
     public DateTime UpdatedAt { get; set; } 
 }

  2. I tried data annotation and set default value like this but didn't meet the requirement.

     public class Author
 {
     [DatabaseGenerated(DatabaseGeneratedOption.Computed)]
     public DateTime UpdatedAt { get; set; }  = DateTime.UtcNow;
 }

  3. I tried Fluent API, but that didn't meet the requirement, it is setting value during insert but not modifying it during update.

     modelbuilder.Entity<Author>()
             .Property(a => a.UpdatedAt)
             .HasDefaultValueSql("GetUtcDate()")
             .ValueGeneratedOnAddOrUpdate();

  4. I tried Fluent API again, but didn't meet the requirement. Neither setting value nor modifying it.

     modelbuilder.Entity<Author>()
             .Property(a => a.UpdatedAt)
             .ValueGeneratedOnAddOrUpdate();

  5. I tried Fluent API yet again, but didn't meet the requirement. It is updating all the rows of that column when ever there is insert or update.

     modelbuilder.Entity<Author>()
             .Property(a => a.UpdatedAt)
             .HasComputedColumnSql("GetUtcDate()");

Upvotes: 0

Views: 247

Answers (1)

Zohar Peled
Zohar Peled

Reputation: 82524

I'm not an EF expert, but according to documentation, datetime values generated on update needs to be created via triggers (Which is consistent of what I know about SQL Server).

In SQL Server, the way to do this is to write a trigger for insert and update that will update the specified column.

Please note, however, that the trigger example provided in documentation has a flaw in it as it assumes only single-row updates. In SQL Server, triggers are fired based on statements, not rows, so when you update multiple rows (or instances, in EF, for that matter), your trigger is fired once.
So the correct way to write it would be this

CREATE TRIGGER [dbo].[Author_UPDATE] ON [dbo].[Author]
    AFTER UPDATE
AS
BEGIN
    SET NOCOUNT ON;

    IF ((SELECT TRIGGER_NESTLEVEL()) > 1) RETURN;

    UPDATE A
    SET UpdatedAt = GETDATE()
    FROM Author As A
    INNER JOIN Inserted As I
        ON A.Id = I.Id;
END

Alternatively, you can handle the DbContext.SavingChanges Event to change the values of UpdatedAt in all your entities that are to be saved, as shown in this example (though the example is handlibg different events [Tracked and StateChanged], not sure it's correct) in the .NET Events in EF Core documentation page.

Upvotes: 1

Related Questions