Efcore: Understanding the ValueGeneratedOnAdd feature

[JonPSmith]

Steps to reproduce

I am trying to build an example of .ValueGeneratedOnAdd() and .ValueGeneratedOnAddOrUpdate() for a book. The .ValueGeneratedOnAddOrUpdate() property doesn't seem to be updated.

The issue

Summary: I have a CreatedOn property with the .ValueGeneratedOnAdd() setting and UpdatedOn property with the .ValueGeneratedOnAddOrUpdate() setting. If I create an entry, wait a second, and update that entry the UpdatedOn is not updated. I also notice the SQL database column UpdatedOn isn't changing.

I assume I have the setup wrong, but I don't see where. You input would be appreciated.

My DbContext is:

public class EfCoreContext : DbContext             
{
    public DbSet<BookAndAuthor> 
        BookAndAuthors { get; set; }               

    public EfCoreContext(
        DbContextOptions<EfCoreContext> options)   
        : base(options)
    { }

    protected override void 
        OnModelCreating(ModelBuilder modelBuilder) 
    {
        modelBuilder.Entity<BookAndAuthor>()
            .Property(x => x.CreatedOn)
            .HasDefaultValueSql("getutcdate()")
            .ValueGeneratedOnAdd();

        modelBuilder.Entity<BookAndAuthor>()
            .Property(x => x.UpdatedOn)
            .HasDefaultValueSql("getutcdate()")
            .ValueGeneratedOnAddOrUpdate();

        ...//setting for other properties left out
        base.OnModelCreating(modelBuilder);
    }
}

My class is:

public class BookAndAuthor
{
    public int BookAndAuthorId { get; set; }  
    public DateTime CreatedOn { get; set; }
    public DateTime UpdatedOn { get; set; }
   ... //other properties removed     
}

My Unit Test is:

[Fact]
public void TestDefaultValueAddOrUpdateOk()
{
    //SETUP
    var connection = this.GetUniqueDatabaseConnectionString();
    var optionsBuilder =
        new DbContextOptionsBuilder<EfCoreContext>();

    optionsBuilder.UseSqlServer(connection);
    var options = optionsBuilder.Options;

    using (var db = new EfCoreContext(options))
    {
        db.Database.EnsureCreated();
        var books = EfTestData.CreateFourBooks();
        db.BookAndAuthors.AddRange(books);
        db.SaveChanges();
        Thread.Sleep(1000);

        //ATTEMPT 
        var firstBook = books.First();
        firstBook.Description = $"has changed on {DateTime.UtcNow:u}";
        db.SaveChanges();

        //VERIFY                                    
        firstBook.UpdatedOn.ShouldNotEqual(firstBook.CreatedOn);
    }
}

The test fails, as the UpdatedOn is the same as the CreatedOn value. Looking at the time in the description the UpdatedOn value has not been updated.

If you are seeing an exception, include the full exceptions details (message and stack trace).
n/a

Further technical details

EF Core version: "Microsoft.EntityFrameworkCore.SqlServer": "1.1.0-preview1-final",
Operating system: Windows 10
Visual Studio version: Microsoft Visual Studio Professional 2015
Version 14.0.25425.01 Update 3
Microsoft .NET Framework
Version 4.6.01586

Other details about my project setup: Pretty normal setup.

[ajcvickers]

@JonPSmith You need to use HasComputedColumnSql rather than HasDefaultValueSql. Also, you don't actually need the calls to ValuGeneratedOnAdd or ValueGeneratedOnAddOrUpdate because when you call HasDefaultValueSql it automatically sets ValuGeneratedOnAdd and when you call HasComputedColumnSql it automatically sets ValueGeneratedOnAddOrUpdate. So your code should be:

``` C#
modelBuilder.Entity()
.Property(x => x.CreatedOn)
.HasDefaultValueSql("getutcdate()");

modelBuilder.Entity()
.Property(x => x.UpdatedOn)
.HasComputedColumnSql("getutcdate()");

```

[JonPSmith]

Thanks @ajcvickers . I was obviously lost there.

It turns out that I misunderstood how the .HasComputedColumnSql() command works. I assumed it placed a method that would be executed at the time that the row was read (i.e. the column flag Is Persisted is false). However because you wrap the command in brackets it is executed on save.

That is fine, but I think it loses one feature that could be useful - that of calling a User Defined Function to dynamically compute some feature, like SELECT COUNT(*) WHERE ... . Maybe something to think about later (much later!): adding an optional flag to control the brackets or the Is Persisted flag would give most scope.

Anyway, now I know. Thanks for the learning.

[JonPSmith]

PS. Another simpler solution. I may not understand this completely (very possible:)), but leaving the brackets off from around the SQL command would allow the developer to make it immediate by putting brackets around it, or execute on read without brackets.

I obviously didn't (still don't) understand all the features of SQL computed columns. My testing shows that brackets don't affect UDFs. What I still can't understand is that (getutcdate()) returns the date that the row was updated, not the current date!

OK. I finally get it. I can't use a computed column to remember the last updated. It returns the current datetime.

Anyway, sorry for my ramblings. I'm getting there.

[trinvh]

Hi, i don't know should I open a new issue so I'll post there.

I have DB context as following:

modelBuilder.Entity<Table>() .Property(b => b.LastUpdate) .HasComputedColumnSql("DATETIME('now', 'localtime')");

Sometimes, when I try to add new or update an existing Table object it throws "The property 'LastUpdate' on entity type 'Table' is defined to be read-only after it has been saved, but its value has been modified or marked as modified."

Why? I didn't update LastUpdate field anywhere.

Any help/suggestion will be appreciated!

[ajcvickers]

@trinvh Can you please open a new issue for this including a full code listing or project that reproduces the problem.