How to use SQL Server - Temporal tables with EntityFramework Core

Question

So, First I created a Temporal (System Versioned) table in SQL Server. For simplicity:

CREATE TABLE dbo.person
(
    ID INT NOT NULL IDENTITY(1,1) PRIMARY KEY CLUSTERED,
    NAME VARCHAR(10) NOT 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.person_history));

GO

Then I used Scaffold-DbContext command to create the objects. So it created 1 object:

public partial class person
{
    public int ID { get; set; }
    public string NAME { get; set; }
    public DateTime SysStartTime { get; set; }
    public DateTime SysEndTime { get; set; }
}

Now I want to save a new item to this table, and here is the problem.

using (var context = new myDBContext())
{
    var newItem = new person
    {
        NAME = "Sergey"
    };

    context.person.Add(newItem);

    context.SaveChanges();
}

Here is the exception from the SaveChanges() line:

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

So my question is, what am I missing? Should I add any settings to specify that this table is system versioned?

When I insert data with a script, directly to the table, it works perfectly.

Answer 1

Update manually the OnModelCreating function in the context file, add these two lines to the person entity:

entity.Property(e => e.SysStartTime).ValueGeneratedOnAddOrUpdate();
entity.Property(e => e.SysEndTime).ValueGeneratedOnAddOrUpdate();

Now, these values will be generated always by SQL, when you add or update an entity.

And you can insert and update data into the person table

If you want to access the history table, you will need another extension, which is described here: https://stackoverflow.com/a/69305781/3281522

And taken from this source: https://github.com/glautrou/EfCoreTemporalTable