Efcore: Cannot explicity initialize values generated on add or update while using data seeding.

[anton-matsyshyn]

There is entity in my project with two generated values (Created and Modified):

        [Key]
        public long Id { get; set; }

        [DatabaseGenerated(DatabaseGeneratedOption.Identity)]
        public DateTime Created { get; set; }

        [DatabaseGenerated(DatabaseGeneratedOption.Computed)]
        public DateTime Modified { get; set; }

        public string FirstName { get; set; }

        public string LastName { get; set; }

        public int Age { get; set; }

I'm trying to seed test data into this table and explicity initialize Created and Modified properties:

modelBuilder.Entity<User>().HasData(new User[]
            {
                new User{Id=1, FirstName="John", LastName="Gold", Age=27, Created=DateTime.Now, Modified=DateTime.Now},
                new User{Id=2, FirstName="Ronald", LastName="Weasley", Age=16, Created=DateTime.Now, Modified=DateTime.Now},
                new User{Id=3, FirstName="Sherlock ", LastName="Holmes", Age=18, Created=DateTime.Now, Modified=DateTime.Now}
            });

But property Modified is not initialized in migration:

migrationBuilder.InsertData(
      table: "Users",
      columns: new[] { "Id", "Age", "Created", "FirstName", "LastName" },
      values: new object[] { 1L, 27, new DateTime(2020, 4, 16, 12, 13, 34, 841, DateTimeKind.Local).AddTicks(4524), "John", "Gold" });

migrationBuilder.InsertData(
      table: "Users",
      columns: new[] { "Id", "Age", "Created", "FirstName", "LastName" },
      values: new object[] { 2L, 16, new DateTime(2020, 4, 16, 12, 13, 34, 845, DateTimeKind.Local).AddTicks(126), "Ronald", "Weasley" });

migrationBuilder.InsertData(
      table: "Users",
      columns: new[] { "Id", "Age", "Created", "FirstName", "LastName" },
      values: new object[] { 3L, 18, new DateTime(2020, 4, 16, 12, 13, 34, 845, DateTimeKind.Local).AddTicks(199), "Sherlock ", "Holmes" });

Of course, I'm getting exception when trying to update database:

Cannot insert the value NULL into column 'Modified', table 'SeedDataBug.dbo.Users'; column does not allow nulls. INSERT fails.

Steps to reproduce

1. Create an entity with generated values on add or update.
2. Use data seeding and try to set generated values explicity.

Simplified project

You can see that bug on this simple project.

Further technical details

• EF Core version: 3.1.3
• Database provider: Microsoft.EntityFrameworkCore.SqlServer
• Target framework: .NET Core 3.1
• Operating system: Windows 10
• IDE: Visual Studio 2019 16.4

[ajcvickers]

@MatsyshynAnton A property marked with [DatabaseGenerated(DatabaseGeneratedOption.Computed)] is expected to be mapped to a computed column in the database. In EF Core, this is typically done using .HasComputedColumnSql("sql here"). Migrations will then create a computed column and EF will not attempt to write values to it--since the values are computed on the server each time a row is read.

You won't be able to seed values for these columns since SQL Server does not allow that. (Also note that using a computed column for a "Modified" stamp is usually not a good idea since it will get a new value each time the row is queried.)

[anton-matsyshyn]

@ajcvickers thank you a lot, you save me from hours of debug. I thought, that I have to specified generated values, because in data seeding I have to initialize all of properties (Id is also generated value, but this article says, that I have to specify it too). I attach fixed code for people, who will face with similar problem.

First of all, you have to set computed colums in OnModelCreating:

modelBuilder.Entity<User>(u =>
            {
                u.Property<DateTime>("Created")
                    .HasComputedColumnSql("GETDATE()");

                u.Property<DateTime>("Modified")
                    .HasComputedColumnSql("GETDATE()");
            });

After that you can add seed data (of course, without specifying generated values):

modelBuilder.Entity<User>().HasData(new User[]
            {
                new User{Id=1, FirstName="John", LastName="Gold", Age=27},
                new User{Id=2, FirstName="Ronald", LastName="Weasley", Age=16},
                new User{Id=3, FirstName="Sherlock ", LastName="Holmes", Age=18}
            });

It works for me.

[anton-matsyshyn]

@ajcvickers , by the way, is it possible to create generated value on add or update via EF Core (as you said, Modified value change every time row is queried), or for this purpose I have to set up directly SQL-server?

[ajcvickers]

@MatsyshynAnton Good question! Unfortunately, it's not that easy. You can override SaveChanges and set the value before calling the base SaveChanges. However, this will be a client timestamp, not a database timestamp. If you need it in the database, then I believe you need to setup a trigger in the SQL Server database. If you do that and then also mark the column as Computed in EF, then I think it should give you what you're looking for.

[anton-matsyshyn]

@ajcvickers Ok, I think that SaveChanges overload is better way for my project. I can use DbContext property ChangeTracker for getting all modified entities. Thank you for response! :)

[jmoralesv]

@ajcvickers Ok, I think that SaveChanges overload is better way for my project. I can use DbContext property ChangeTracker for getting all modified entities. Thank you for response! :)

Thanks for this comment, @MatsyshynAnton , I'm currently working on an ASP.NET Core application where I have to set the modified date for most of my entities that inherit from a base class. Your comment helped me a lot. Thank you!

[anton-matsyshyn]

@jmoralesv , glad I helped you :) Have a nice coding