We are using system-versioned temporal table in our Entity Framework Core application. This works really well but we are experiencing problems when creating a test.

<https://learn.microsoft.com/en-us/sql/relational-databases/tables/temporal-tables?view=sql-server-ver15>

I have been following this guide using SQLite in-memory databases to test an EF Core application from Microsoft.

<https://learn.microsoft.com/en-us/ef/core/testing/sqlite#using-sqlite-in-memory-databases>

The problem is that `Sqlite` will throw an exception for `SysStartTime`. This is expected since the property is marked as `prop.ValueGenerated = Microsoft.EntityFrameworkCore.Metadata.ValueGenerated.OnAddOrUpdate;` in `DbContext` and is normally handled by Sql Server. Is there anyway to make this work in SQLite?

> 
> SqliteException: SQLite Error 19: 'NOT NULL constraint failed:
> User.SysStartTime'.
> 
> 
> 

[![enter image description here](https://i.stack.imgur.com/g8qyT.png)](https://i.stack.imgur.com/g8qyT.png)

User:

```
public class User : IEntity
{
    [DatabaseGenerated(DatabaseGeneratedOption.Identity)]
    public int Id { get; set; }

    public DateTime SysStartTime { get; set; }

    public DateTime SysEndTime { get; set; }

    [Required]
    public string ExternalId { get; set; }
}

```

xUnit Test:

```
public class QuestionUpdateTest: IDisposable
{
    private readonly DbConnection _connection;
    private readonly ApplicationDbContext _context = null;

    public ChoiceSequencingQuestionUpdateTest()
    {
        var dbContextOptions = new DbContextOptionsBuilder<ApplicationDbContext>()
                        .UseSqlite(CreateInMemoryDatabase())
                        .Options;

        _connection = RelationalOptionsExtension.Extract(dbContextOptions).Connection;

        _context = new ApplicationDbContext(dbContextOptions);

        _context.User.Add(new User()
        {
            ExternalId = "1"
        });

        _context.SaveChangesNoUser();
    }

    private static DbConnection CreateInMemoryDatabase()
    {
        var connection = new SqliteConnection("Filename=:memory:");

        connection.Open();

        return connection;
    }

    public void Dispose() => _connection.Dispose();

    [Fact]
    public void Test2()
    {

    }
}

```

ApplicationDbContext:

```
public int SaveChangesNoUser()
{
    //Wont help since the property is marked as ValueGenerated
    foreach (var changedEntity in ChangeTracker.Entries())
    {
        if (changedEntity.Entity is IEntity entity)
        {
            switch (changedEntity.State)
            {
                case EntityState.Added:
                    entity.SysStartTime = DateTime.Now;
                    break;
            }
        }
    }

    return base.SaveChanges();
}

protected override void OnModelCreating(ModelBuilder modelBuilder)
{
    foreach (var property in modelBuilder.Model.GetEntityTypes()
        .SelectMany(t => t.GetProperties())
        .Where(p => p.ClrType == typeof(string)))
    {
        if (property.GetMaxLength() == null)
            property.SetMaxLength(256);
    }

    foreach (var property in modelBuilder.Model.GetEntityTypes()
        .SelectMany(t => t.GetProperties())
        .Where(p => p.ClrType == typeof(DateTime)))
    {
        property.SetColumnType("datetime2(0)");
    }

    foreach (var et in modelBuilder.Model.GetEntityTypes())
    {
        foreach (var prop in et.GetProperties())
        {
            if (prop.Name == "SysStartTime" || prop.Name == "SysEndTime")
            {
                prop.ValueGenerated = Microsoft.EntityFrameworkCore.Metadata.ValueGenerated.OnAddOrUpdate;
            }
        }
    }

    base.OnModelCreating(modelBuilder);
}

```

Migration:

```
public partial class Temporaltablesforallentities : Migration
{
    List<string> tablesToUpdate = new List<string>
        {
           "User",
        };

    protected override void Up(MigrationBuilder migrationBuilder)
    {
        foreach (var table in tablesToUpdate)
        {
            string alterStatement = $@"ALTER TABLE [{table}] 
                ADD PERIOD FOR SYSTEM_TIME ([SysStartTime], [SysEndTime])";
            migrationBuilder.Sql(alterStatement);
            alterStatement = $@"ALTER TABLE [{table}] 
                SET (SYSTEM_VERSIONING = ON (HISTORY_TABLE = History.[{table}], DATA_CONSISTENCY_CHECK = ON));";
            migrationBuilder.Sql(alterStatement);
        }
    }

    protected override void Down(MigrationBuilder migrationBuilder)
    {
        foreach (var table in tablesToUpdate)
        {
            string alterStatement = $@"ALTER TABLE [{table}] SET (SYSTEM_VERSIONING = OFF);";
            migrationBuilder.Sql(alterStatement);
            alterStatement = $@"ALTER TABLE [{table}] DROP PERIOD FOR SYSTEM_TIME";
            migrationBuilder.Sql(alterStatement);
            alterStatement = $@"DROP TABLE History.[{table}]";
            migrationBuilder.Sql(alterStatement);
        }
    }
}

```

<https://learn.microsoft.com/en-us/sql/relational-databases/tables/creating-a-system-versioned-temporal-table?view=sql-server-ver15#important-remarks-4>