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.

Spindling

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 – Goofball

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. – Biggin

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 – Goofball

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. – Twelvemo

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! – Spindling

There is a detailed introduction into temporal tables support in release notes of EF Core 6: learn.microsoft.com/en-us/ef/core/what-is-new/ef-core-6.0/… – Storybook

Answer 1

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

Aught

IF its a M-M table only having the Key of the callers and If I dont have a cs model for it this wont generate for it is there a way to achieve it for those M-M tables ? – Parsifal

I tested this. GetEntityTypes also gets the hidden junction table. It's generated as a temporal table with non-cascading foreign keys. – Aught

Answer 2

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://mcmap.net/q/591049/-ef-core-6-0-temporal-tables-add-migration-period-property-39-comment-periodstart-39-must-be-a-shadow-property

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

Symons