How to Auto Compute Date Time field in SQL server

Question

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

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

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

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;
 }

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();

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();

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()");

Answer 1

Hi @net 6 newbie，

Below is a work demo, you can refer to:

Author :

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

     public string Name { get; set; }    
     public int Id { get; set; }
 }

Then add override SaveChanges in dbContext like:

public class ComputeCore6Context : DbContext
{
    public ComputeCore6Context (DbContextOptions<ComputeCore6Context> options)
        : base(options)
    {
    }

    public DbSet<ComputeCore6.Models.Author> Author { get; set; } = default!;
    public override int SaveChanges()
    {
        var entries = ChangeTracker
            .Entries()
            .Where(e => e.Entity is Author && (
                e.State == EntityState.Added
                || e.State == EntityState.Modified));

        foreach (var entry in entries)
        {
            var entity = (Author)entry.Entity;

            if (entry.State == EntityState.Added)
            {
                entity.UpdatedAt = DateTime.UtcNow;
            }

            entity.UpdatedAt = DateTime.UtcNow;
        }

        return base.SaveChanges();
    }
}

In the controller use SaveChanges:

  _context.Update(author);
  _context.SaveChanges();

result:

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

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.

Best regards,

Qing Guo

Answer 2

Sql column defaults only work on insert if the column is not specified in the insert. For EF, if you pass null on insert and defined a default value for the column in EF, EF will use the default value.

as suggested for your needs you should use stored procedures or triggers.

see docs:

https://learn.microsoft.com/en-us/ef/core/modeling/generated-properties?tabs=data-annotations

note: a computed column will not work, because the compute is done on every fetch.

Answer 3

Hello,

you should use a trigger (after Insert, update), this way you can put the timestamp on an specific column

https://learn.microsoft.com/en-us/sql/t-sql/statements/create-trigger-transact-sql?view=sql-server-ver16

Regards

Javier