Error when inserting into temporal table using Entity Framework Core 2.1

Question

I'm getting the below error when trying to insert into a temporal table using Entity Framework Core 2.1.

Cannot insert an explicit value into a GENERATED ALWAYS column in table 'db_test.dbo.Department'. Use INSERT with a column list to exclude the GENERATED ALWAYS column, or insert a DEFAULT into GENERATED ALWAYS column.

Below is my table schema

CREATE TABLE Department   
(    
     DeptID int NOT NULL PRIMARY KEY CLUSTERED, 
     DeptName varchar(50) NOT NULL,
     ManagerID INT  NULL,
     ParentDeptID int NULL,
     SysStartTime datetime2 GENERATED ALWAYS AS ROW START NOT NULL,
     SysEndTime datetime2 GENERATED ALWAYS AS ROW END NOT NULL, 
     PERIOD FOR SYSTEM_TIME (SysStartTime, SysEndTime)     
)   
WITH    
   (   
      SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.DepartmentHistory)   
   )   
;

And my DbContext looks like this:

modelBuilder.Entity<Department>(entity =>
            {
                entity.HasKey(e => e.DeptId);

                entity.Property(e => e.DeptId)
                    .HasColumnName("DeptID")
                    .ValueGeneratedNever();

                entity.Property(e => e.DeptName)
                    .IsRequired()
                    .HasMaxLength(50)
                    .IsUnicode(false);

                entity.Property(e => e.ManagerId).HasColumnName("ManagerID");

                entity.Property(e => e.ParentDeptId).HasColumnName("ParentDeptID");
            });

Please take a look and let me know how we can fix this. I'm not a fan of modifying the db context class as I generate it via nuget console using DBScaffold and I cannot update it manually each time when we re-generate it.

Answer 1

Making the Period start column(SysStartTime) and Period end column(SysEndTime) should fix this issue. We can do this by

ALTER TABLE [dbo].[Department] ALTER COLUMN [SysStartTime] ADD HIDDEN;
ALTER TABLE [dbo].[Department] ALTER COLUMN [SysEndTime] ADD HIDDEN;

We can see the settings for hidden against these columns in the sys.columns table

SELECT * FROM sys.columns WHERE is_hidden = 1