c# - SQLite 内存数据库使用时态表测试 EF Core 应用程序

Question

我们在 Entity Framework Core 应用程序中使用系统版本化的时态表。这非常有效，但是我们在创建测试时遇到了问题。

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

我一直在按照本指南使用 SQLite 内存数据库来测试 Microsoft 的 EF Core 应用程序。

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

问题是它Sqlite会抛出一个异常SysStartTime。这是意料之中的，因为该属性被标记为prop.ValueGenerated = Microsoft.EntityFrameworkCore.Metadata.ValueGenerated.OnAddOrUpdate;inDbContext并且通常由 Sql Server 处理。无论如何可以在 SQLite 中完成这项工作吗？

SqliteException：SQLite 错误 19：'NOT NULL 约束失败：User.SysStartTime'。

用户：

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测试：

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()
    {
        
    }
}

应用程序上下文：

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);
}

移民：

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://docs.microsoft.com/en-us/sql/relational-databases/tables/creating-a-system-versioned-temporal-table?view=sql-server-ver15#important-remarks-4

标签： c#sqliteentity-framework-corexunittemporal-tables

Answer 1

像这样解决它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);
            }

        }
    }
}