EF Core 6 and Temporal Tables - Set every entity as IsTemporal

Question

I've been tasked with figuring out the impact of enabling temporal tables in our database. So I've been looking for a feature to enabling it to the entire database, and not just with:

protected override void OnModelCreating(ModelBuilder modelBuilder)
{
    modelBuilder.Entity<Employee>()
                .ToTable("Employees", e => e.IsTemporal());
}

I haven't been able to find anything about this, is there anything that will just let me select the entire database, and then do it for each table?

I've downloaded some bigger database datasets to test the impact on. Hopefully, I can find a way to automate the conversion, so I don't have to manually apply the code to 100's of tables.

Write the SQL statements. EF Core isn't even a data access library, it's just an ORM that generates SQL statements at some point. ORM migrations only work for *very* simple situations and as you found out, don't support all database features of every database — Panagiotis Kanavos, Aug 25 '22 at 13:51
You don't create "temporal databases", just temporal tables. You don't enable the feature and then *all* the tables automagically are temporal tables; you need to make a table a temporal table. That means that for any tables you want to be temporal (which is very unlikely to be *every* table in your database) you need to specifically define it to be one. — Thom A, Aug 25 '22 at 13:56
`a feature to enabling it to the entire database` there's no such feature and would be a very, very bad idea anyway. Only *some* tables in any database need to be versioned. These will need different options. For example: anonymous history table, default or manual? If you want to query historical records you can't use an anonymous table. For such administration tasks the correct tool is a SQL script that performs the changes you want. You can use eg `select table_name from INFORMATION_SCHEMA.Tables` to get the names of the tables you want and use code to generate the change script for each one — Panagiotis Kanavos, Aug 25 '22 at 13:57
Keep in mind that a temporal table is really two tables (the main table and the history table) with special logic for maintaining the two in queries. Given this you'll probably see why a feature to automatically create hundreds of new tables and/or dropping them would not be such a great idea (especially since effectively supporting temporal queries requires some consideration with index creation). Depending on your exact scenario, there are alternatives if you just want to have temporal identities for a whole database, like snapshots and point-in-time log backups. — Jeroen Mostert, Aug 25 '22 at 14:00
Thank you all for the comments. You did answer some extra questions I had in my head. So ill figure out which tables would be the correct ones to create temporal tables to! — SeraNoxa, Aug 25 '22 at 14:04
There is a detailed introduction into temporal tables support in release notes of EF Core 6: https://learn.microsoft.com/en-us/ef/core/what-is-new/ef-core-6.0/whatsnew — Yuriy Gavrishov, Aug 27 '22 at 19:33

score 0 · Answer 1 · answered Feb 18 '23 at 21:43

We started out doing it like this for every entity:

protected override void OnModelCreating(ModelBuilder modelBuilder)
{
    SetTemportalTables(modelBuilder);
}
        
private void SetTemportalTables(ModelBuilder modelBuilder)
{
    modelBuilder.Entity<Customer>().ToTable(tb => tb.IsTemporal());
    modelBuilder.Entity<Product>().ToTable(tb => tb.IsTemporal());
    modelBuilder.Entity<Orders>().ToTable(tb => tb.IsTemporal());
}

https://stackoverflow.com/a/70600449/3850405

We did however end up wanting every entity that implemented the interface IEntity to have SQL Server temporal tables and we solved that like this:

protected override void OnModelCreating(ModelBuilder modelBuilder)
{
    foreach (var property in modelBuilder.Model.GetEntityTypes())
    {
        if (property.ClrType.IsAssignableTo(typeof(IEntity)))
        {
            property.SetIsTemporal(true);
        }
    }
}

score 0 · Answer 2 · answered Feb 18 '23 at 23:00

Since companies can be extremely slow in upgrading SQL server (understandably) and SQL Server 2016 doesn't support cascading foreign keys on temporal tables, let me just show how to set types as temporal and also remove cascaded delete from their foreign keys:

protected override void OnModelCreating(ModelBuilder modelBuilder)
{
    var types = modelBuilder.Model.GetEntityTypes().Where(...).ToList();
    // When on SQL server 2016 (no cascading FKs allowed on temporal tables)
    foreach (var relationship in types.SelectMany(e => e.GetForeignKeys()))
    {
        relationship.DeleteBehavior = DeleteBehavior.Restrict;
    }
    
    types.ForEach(t => t.SetIsTemporal(true));
}

EF Core 6 and Temporal Tables - Set every entity as IsTemporal

2 Answers2

Linked