Don't update column in ms sql database

[Tishka]

Hello.

I have next c# class models:

public abstract class EstimateBase
    {
        private DateTime? _changedAt;

        [Required]
        [Description("Идентификатор (ID) объекта в ИСУП КС")]
        public virtual int OBJ_ID { get; set; }

        //[DatabaseGenerated(DatabaseGeneratedOption.Computed)] //TODO: видимо не работает в EF.Core
        public DateTime ChangedAt
        {
            get { return (_changedAt == null || _changedAt == DateTime.MinValue) ? DateTime.Now : (DateTime)_changedAt; }
            set { _changedAt = value; }
        }

        [Required]
        [Description("Версия")]
        public int VERSN { get; set; }
    }

and his heir:

public abstract class EstimateSpecificationBase : EstimateBase
{
        [Description("Наименование объекта, Наименование элемента структуры")]
        [MaxLength(255)]
        public string FullName { get; set; }

        [MaxLength(255)]
        public string SHIFR { get; set; }
}

public class EstimateHeader : EstimateSpecificationBase
{
...
}

I want the value of the "public DateTime ChangedAt" column to change automatically when a record is inserted / changed in the table. I am add [DatabaseGenerated(DatabaseGeneratedOption.Computed)] attribyte or add next code in method protected override void OnModelCreating(ModelBuilder modelBuilder) of dbContext :

modelBuilder.Entity<EstimateHeader>(entity =>
{
            entity.Property(p => p.ChangedAt).ValueGeneratedOnAddOrUpdate();
});

But, how i read this is not work for DateTime column and depend on db provider behavior. Maybe you can say me, how i can do this?

Then i added next code for class property:

private DateTime? _changedAt;
public DateTime ChangedAt
        {
            get { return (_changedAt == null || _changedAt == DateTime.MinValue) ? DateTime.Now : (DateTime)_changedAt; }
            set { _changedAt = value; }
        }

Db migration for this table is:

migrationBuilder.CreateTable(
                name: "EstimateHeaders",
                columns: table => new
                {
                    OBJ_ID = table.Column<int>(type: "int", nullable: false),
                    ...
                    ChangedAt = table.Column<DateTime>(type: "datetime2", nullable: false),
                    VERSN = table.Column<int>(type: "int", nullable: false)
                },
                constraints: table =>
                {
                    table.PrimaryKey("PK_EstimateHeaders", x => x.OBJ_ID);
                });

Then i insert entity in table, in column ChangedAt i have value: '0001-01-01 00:00:00.0000000' Insert in db as:

public virtual TEntity Insert(TEntity entity)
{
            entity = Context.Set<TEntity>().Add(entity).Entity;

            try
            {
                Context.SaveChanges();
            }
            catch (Exception ex)
            {
                _logger.LogCritical("Ошибка сохранения", ex);
                throw;
            }

            return entity;
}

Use SQL Server Profiler i get next request:

exec sp_executesql N'SET NOCOUNT ON;
INSERT INTO [EstimateHeaders] ([OBJ_ID], [CDATETIME], [COSTLEVEL_ID], [COSTLEVEL_LMONTH], [COSTLEVEL_LPERIOD], [COSTLEVEL_LYEAR], [COSTLEVEL_NAME], [ChangedAt], [DEL], [DOCNUMBER], [DOCVERSION], [DOP_N], [DOSTX], [FullName], [GLAVA], [ID_PROJ], [ISCLC], [IZM_N], [KKS_KOD], [LSR_N], [LSR_SUF], [LS_MDS], [LS_N], [LS_SUF], [OBJNR], [OBJ_N], [OBJ_TYPE], [POSID], [PRC_TYPE], [SHIFR], [STABK], [STATUS], [STR_UNIT], [STR_UNIT_NAME], [SUBOBJ_N], [TYPE], [VERSN])
VALUES (@p0, @p1, @p2, @p3, @p4, @p5, @p6, @p7, @p8, @p9, @p10, @p11, @p12, @p13, @p14, @p15, @p16, @p17, @p18, @p19, @p20, @p21, @p22, @p23, @p24, @p25, @p26, @p27, @p28, @p29, @p30, @p31, @p32, @p33, @p34, @p35, @p36);
',N'@p0 int,@p1 datetime2(7),@p2 int,@p3 tinyint,@p4 tinyint,@p5 smallint,@p6 nvarchar(255),@p7 datetime2(7),@p8 bit,@p9 nvarchar(25),@p10 nvarchar(2),@p11 int,@p12 nvarchar(16),@p13 nvarchar(255),@p14 smallint,@p15 int,@p16 smallint,@p17 int,@p18 nvarchar(100),@p19 int,@p20 nvarchar(1),@p21 nvarchar(100),@p22 int,@p23 nvarchar(2),@p24 nvarchar(22),@p25 smallint,@p26 nvarchar(3),@p27 nvarchar(24),@p28 nvarchar(1),@p29 nvarchar(255),@p30 nvarchar(2),@p31 nvarchar(5),@p32 int,@p33 nvarchar(40),@p34 smallint,@p35 smallint,@p36 int',@p0=552702626,@p1='2019-12-03 10:47:59',@p2=552702681,@p3=1,@p4=0,@p5=2000,@p6=N'Уровень ',@p7='0001-01-01 00:00:00',@p8=0,@p9=N'0000000000000010000094414',@p10=N'01',@p11=0,@p12=NULL,@p13=N'Основные объекты',@p14=2,@p15=2,@p16=0,@p17=0,@p18=N'0120.11UPZ.0.EW.LH0001.L0002',@p19=0,@p20=NULL,@p21=N'02-25.1-0011Э-2Э',@p22=11,@p23=NULL,@p24=N'LS5',@p25=25,@p26=N'LS',@p27=N'C.01-02-025-01',@p28=NULL,@p29=N'02-25.1-0011Э-2Э',@p30=N'Д5',@p31=N'E0006',@p32=NULL,@p33=NULL,@p34=0,@p35=0,@p36=1

Why i get some result? Why field has such value?

If i update this record field ChangedAt updated to class value. Update as:

public virtual TEntity Update(TEntity entity)
 {
            var entry = Context.Entry(entity);
            try
            {
                entry.State = EntityState.Modified;
                Context.SaveChanges();
            }
            catch (Exception ex)
            {
                _logger.LogCritical("Ошибка сохранения", ex);
                throw;
            }

            return entity;
 }

P.S. If i use method HasDefaultValueSql() as:

modelBuilder.Entity<EstimateHeader>(entity =>
{
      entity.Property(p => p.ChangedAt).HasDefaultValueSql("GETDATE()");
}); 

on insert it work fine, but on update column don't changed value from class field.

[roji]

@Tishka you probably want to read our docs on generated values, and especially the section on date/time value generation.

tl;dr to set the date/time every time the row is updated, you will need to set up a database trigger (just configuring ValueGeneratedOnAddOrUpdate doesn't set up this mechanism in the database).

In your code above, when an entity is loaded from the database, ChangedAt is loaded along with it. When the application then makes a change, ChangeAt isn't recalculated at any point, and so EF doesn't detect any changes and so doesn't save the value to the database (even if it did, it again would be the old value since no recalculation took place).

[Tishka]

@Tishka you probably want to read our docs on generated values, and especially the section on date/time value generation.

I was reading this section, but can't use and create this. Tried use HasDefaultValueSql("GETDATE()"), but this works on first insert, but not update

tl;dr to set the date/time every time the row is updated, you will need to set up a database trigger (just configuring ValueGeneratedOnAddOrUpdate doesn't set up this mechanism in the database).

we don't use logic in db...

In your code above, when an entity is loaded from the database, ChangedAt is loaded along with it. When the application then makes a change, ChangeAt isn't recalculated at any point, and so EF doesn't detect any changes and so doesn't save the value to the database (even if it did, it again would be the old value since no recalculation took place).

If i don't modify ChangeAt this is understand. But then i create new entity, this field is calculated, but not saved

[roji]

Tried use HasDefaultValueSql("GETDATE()"), but this works on first insert, but not update

This is the expected behavior. Databases don't support computed columns with GETDATE().

we don't use logic in db...

Note that HasDefaultValueSql is another form of DB logic, so if HasDefaultValueSql is acceptable, a trigger isn't very different.

If i don't modify ChangeAt this is understand. But then i create new entity, this field is calculated, but not saved

This is probably because you have ValueGeneratedOnAddOrUpdate on the property, so EF assumes the value will be generated (e.g. in the database) and so doesn't send the property's actual value. If you remove ValueGeneratedOnAddOrUpdate, then you should see the value getting sent, but once again, this will only work for new entities. Entities which you load from the database won't have their property recalculated.

If you really wish to avoid database triggers, then it's up to you to trigger recalculation of the property; you could either do it manually in code, or set up some mechanism where changing any other property would also cause ChangeAt to be recalculated.

[Tishka]

This is probably because you have ValueGeneratedOnAddOrUpdate on the property, so EF assumes the value will be generated (e.g. in the database) and so doesn't send the property's actual value. If you remove ValueGeneratedOnAddOrUpdate, then you should see the value getting sent, but once again, this will only work for new entities. Entities which you load from the database won't have their property recalculated.

I comment this code block, but don't work for data insert.

If you really wish to avoid database triggers, then it's up to you to trigger recalculation of the property; you could either do it manually in code, or set up some mechanism where changing any other property would also cause ChangeAt to be recalculated.

Yes, i think about it

[Tishka]

Note that HasDefaultValueSql is another form of DB logic, so if HasDefaultValueSql is acceptable, a trigger isn't very different.

I can create trigger from code, not in db?

[roji]

I can create trigger from code, not in db?

You can create the trigger by using raw SQL in a migration.

[ajcvickers]

@Tishka Note that if you are okay with the timestamp being generated on the client, then overriding SaveChanges and setting ChangeAt there is easy to implement.

[Tishka]

@Tishka Note that if you are okay with the timestamp being generated on the client, then overriding SaveChanges and setting ChangeAt there is easy to implement.

Hello.

Can you get examples?

[Tishka]

I can create trigger from code, not in db?

You can create the trigger by using raw SQL in a migration.

Why don't add [DatabaseGenerated(DatabaseGeneratedOption.Computed)] for DateTime columns?

[ajcvickers]

@Tishka Here's an example:

public abstract class EstimateBase
{
    private DateTime? _changedAt;

    [Required]
    [Description("Идентификатор (ID) объекта в ИСУП КС")]
    public virtual int OBJ_ID { get; set; }

    public DateTime ChangedAt { get; set; }

    [Required]
    [Description("Версия")]
    public int VERSN { get; set; }
}

public abstract class EstimateSpecificationBase : EstimateBase
{
    [Description("Наименование объекта, Наименование элемента структуры")]
    [MaxLength(255)]
    public string FullName { get; set; }

    [MaxLength(255)]
    public string SHIFR { get; set; }
}

public class EstimateHeader : EstimateSpecificationBase
{
}

public class SomeDbContext : DbContext
{
    private static ILoggerFactory ContextLoggerFactory
        => LoggerFactory.Create(b => b.AddConsole().SetMinimumLevel(LogLevel.Information));

    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
        => optionsBuilder
            .UseSqlServer(Your.ConnectionString)
            .UseLoggerFactory(ContextLoggerFactory)
            .EnableSensitiveDataLogging();

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        modelBuilder.Entity<EstimateBase>().HasKey(e => e.OBJ_ID);
        modelBuilder.Entity<EstimateSpecificationBase>();
        modelBuilder.Entity<EstimateHeader>();
    }

    public override int SaveChanges()
    {
        foreach (var entry in ChangeTracker
            .Entries<EstimateBase>()
            .Where(e => e.State == EntityState.Added || e.State == EntityState.Modified))
        {
            entry.Entity.ChangedAt = DateTime.UtcNow;
        }

        return base.SaveChanges();
    }
}

public class Program
{
    public static void Main()
    {
        using (var context = new SomeDbContext())
        {
            context.Database.EnsureDeleted();
            context.Database.EnsureCreated();

            context.Add(new EstimateHeader());
            context.SaveChanges();
        }

        using (var context = new SomeDbContext())
        {
            var header = context.Set<EstimateHeader>().Single();
            header.FullName = "A";
            context.SaveChanges();
        }
    }
}

[Tishka]

@Tishka Here's an example:

public abstract class EstimateBase
{
    private DateTime? _changedAt;

    [Required]
    [Description("Идентификатор (ID) объекта в ИСУП КС")]
    public virtual int OBJ_ID { get; set; }

    public DateTime ChangedAt { get; set; }

    [Required]
    [Description("Версия")]
    public int VERSN { get; set; }
}

public abstract class EstimateSpecificationBase : EstimateBase
{
    [Description("Наименование объекта, Наименование элемента структуры")]
    [MaxLength(255)]
    public string FullName { get; set; }

    [MaxLength(255)]
    public string SHIFR { get; set; }
}

public class EstimateHeader : EstimateSpecificationBase
{
}

public class SomeDbContext : DbContext
{
    private static ILoggerFactory ContextLoggerFactory
        => LoggerFactory.Create(b => b.AddConsole().SetMinimumLevel(LogLevel.Information));

    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
        => optionsBuilder
            .UseSqlServer(Your.ConnectionString)
            .UseLoggerFactory(ContextLoggerFactory)
            .EnableSensitiveDataLogging();

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        modelBuilder.Entity<EstimateBase>().HasKey(e => e.OBJ_ID);
        modelBuilder.Entity<EstimateSpecificationBase>();
        modelBuilder.Entity<EstimateHeader>();
    }

    public override int SaveChanges()
    {
        foreach (var entry in ChangeTracker
            .Entries<EstimateBase>()
            .Where(e => e.State == EntityState.Added || e.State == EntityState.Modified))
        {
            entry.Entity.ChangedAt = DateTime.UtcNow;
        }

        return base.SaveChanges();
    }
}

public class Program
{
    public static void Main()
    {
        using (var context = new SomeDbContext())
        {
            context.Database.EnsureDeleted();
            context.Database.EnsureCreated();

            context.Add(new EstimateHeader());
            context.SaveChanges();
        }

        using (var context = new SomeDbContext())
        {
            var header = context.Set<EstimateHeader>().Single();
            header.FullName = "A";
            context.SaveChanges();
        }
    }
}

Thank you very match! I will try use