SQLite in-memory databases testing an EF Core application with temporal tables

Question

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'.

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

Answer 1

Solved it like this in protected override void OnModelCreating(ModelBuilder modelBuilder):

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

        }
    }
}

Answer 2

Try removing SysStartTime and SysEndTime from your models. You can add them using the following snippets:

Create a Constants.cs or similar:

        public const string AddSystemVersioningFormatString = @"
            ALTER TABLE [dbo].[{0}] 
            ADD SysStartTime datetime2 GENERATED ALWAYS AS ROW START HIDDEN NOT NULL 
                CONSTRAINT DF_{0}_SysStartTime DEFAULT SYSUTCDATETIME(),
                SysEndTime datetime2 GENERATED ALWAYS AS ROW END HIDDEN NOT NULL 
                CONSTRAINT DF_{0}_SysEndTime DEFAULT CONVERT(datetime2, '9999-12-31 23:59:59.9999999'),
                PERIOD FOR SYSTEM_TIME (SysStartTime, SysEndTime)
            ALTER TABLE [dbo].[{0}] 
            SET (SYSTEM_VERSIONING = ON (
                HISTORY_TABLE = [dbo].[{0}History],
                DATA_CONSISTENCY_CHECK = ON )
        )";
        public const string RemoveSystemVersioningFormatString = @"
            ALTER TABLE [dbo].[{0}] SET (SYSTEM_VERSIONING = OFF)
            ALTER TABLE [dbo].[{0}] DROP PERIOD FOR SYSTEM_TIME
            ALTER TABLE [dbo].[{0}] DROP CONSTRAINT DF_{0}_SysStartTime
            ALTER TABLE [dbo].[{0}] DROP CONSTRAINT DF_{0}_SysEndTime
            ALTER TABLE [dbo].[{0}] DROP COLUMN SysStartTime, SysEndTime
            DROP TABLE IF EXISTS [dbo].[{0}History]
        ";

Then in your migration:

migrationBuilder.Sql(string.Format(Constants.AddSystemVersioningFormatString, "User"));

So your models won't know about the extra columns, and you won't have to explicitly set anything in EF since SQL Server will handle all the setting for you.