SQL Server temporal tables

[shivajiraot]

Does EF Core support code first approach for Temporal tables?
Looking to find a way to use Temporal tables with EF Core code first approach. Please guide me to the article if any existing already.
Thank you.

[rowanmiller]

For the most part, the fact that a table is temporal is opaque to the data layer - so from that point of view you should be fine to connect to an existing database with temporal tables.

Here are some limitations though, that we could address in EF:

• If you wanted to query a past state of the table you would need to use a raw SQL query.
• There is no way to make a table temporal in the model/migrations. So if you are using EF to create the database then you would need to hand edit the migration and use Sql(string) to make the table temporal.

[DavidBainbridgeRIT]

I second this as important. Temporal tables are fantastic. They cut out a lot of boiler plate but I need that information in the front end just as easily as in the back end.

[R00iBaard]

Agree, I would love to be able to set this up all via EF code first.

[reberinformatik]

👍 See also #2229

[lucasmaj]

I was absolutely bummed out to find out that currently there is no obvious way to query past state using lambda expressions. Is there any way to sneak in "AS OF <date_time>" into a query? (in EF6)

[divega]

@lucasmaj have you tried raw SQL queries as a workaround as explained above? E.g. in EF6 use SqlQuery() and FromSql() with EF Core. The latter is composable in LINQ.

[lucasmaj]

I haven't tried raw SQL. If EF6 had SqlQuery composable with LINQ it would do the trick for now. There is no way I would go with pure sql queries and abandon LINQ. Unless there could be a way for LINQ to pure sql back to entities in an automatic fashion.

Is there really no way to augment conversion of Expressions to SQL string so that some custom expression would translate to "AS OF" etc?

Thank you nonetheless.

[sfgadjo]

I look forward to full support of Temporal Tables in an upcoming release. In the meantime, I am trying to make use of the SysStartTime/SysEndTime columns added to the table in the model (using CodeFirst), and I find that EF is trying to write to those columns even when I have the model property annotated with DatabaseGenerated(DatabaseGeneratedOption.Computed). Is there a way to get around this issue so that I can at least use those columns added for versioning without writing SQL?

[R00iBaard]

Any update on this? It would be very nice to have this 👍

[ajcvickers]

@R00iBaard This issue is in the Backlog milestone. This means that it is not going to happen for the 2.0 release. We will re-assess the backlog following the 2.0 release and consider this item at that time. However, keep in mind that there are many other high priority features with which it will be competing for resources.

[ntimmerman]

+1, I would like to use this in the very near future for a new enterprise doc automation app that requires an audit history on every screen. Does anyone have any more information about the work around's for now?

[pray1997]

+1

We need it too!

Or is.there any workaround, maybe something like intercepting?

[ephos]

+1 - I hope this gets prioritized after 2.0, this would be great to have without manual setup!

[chassq]

+1

[kliebenberg]

+1

[angularDevd]

+1

[cSteusloff]

+1

[squeakyD]

If you added support for interceptors (like in EF6), then it would be possible to handle inserts and updates to temporal tables by handling the generated fields.

[smitpatel]

@squeakyD - At present you can configure your model with generated columns to insert/update data in temporal table. Main and most useful functionality would be the query pipeline especially generating SQL with between predicate etc.

[binarypatrick]

+1, would love to see the ability to time slice with a lambda expression.

[sifordtj]

+1. Would also love to see the ability to query data with lambda ... something that gives me the FOR SYSTEM_TIME without have to use a raw SQL string.

[israellot]

+1

[forest-devil]

+1

[AndriySvyryd]

Please vote using the 👍 reaction on the very first post, otherwise it won't affect the order in the issue list https://github.com/aspnet/EntityFrameworkCore/issues?q=is%3Aopen+is%3Aissue+label%3Atype-enhancement+sort%3Areactions-%2B1-desc

[smitpatel]

Please vote using the 👍 reaction on the very first post

corrected a bit.

[jzabroski]

@smitpatel One thing to be cautious of, is to write integration tests that minimize the likelihood of the following error:

System.Data.SqlClient.SqlException: Data modification failed on system-versioned table 'WorldWideImporters.Sales.Customers' because transaction time was earlier than period start time for affected records.

[rosenbjerg]

Just came across this Adam-Langley/efcore-temporal-query.
I like the way temporality it integrated through IQueryable.AsOf(..), and there might be other cool ideas there

[jzabroski]

@maumar Additionally, after discussing with my team, supporting code-first migrations is trickier for temporal tables, under the following scenarios:

1. Adding a new field to a table that has temporal table backed history
2. Changing an existing column from nullable to non-nullable - how do you specify a default value for a historical table? System versioning will need to be temporarily disabled to "patch" the table.
3. If MigrationBuilder.RenameColumn uses sp_rename, renaming a column may not be guaranteed to work under certain circumstances I documented on Azure Voice,due to bugs in sp_rename.

[smitpatel]

@jzabroski - Thanks for bringing that to attention. It would certainly need to be taken care if we allow any kind of modification over history tables directly.

[jzabroski]

The way I see it, altering a column for a table that has a temporal table hacked history should have a consequent of cascading the change to the history table. In my FluentMigrator project, I don't achieve that (yet) but instead just decompose the problem into several recipes - not super elegant in that it requires careful typing, but also successful nonetheless:

            this.DisableSystemVersioning("dbo", "Foo");

            Rename.Column("Unit_Price").OnTable("Foo").InSchema("dbo")
                .To("UnitPrice");
            Rename.Column("Unit_Price").OnTable("FooHistory").InSchema("dbo")
                .To("UnitPrice");
            Delete.DefaultConstraint()
                .OnTable("Foo")
                .InSchema("dbo")
                .OnColumn("UnitPrice");
            Alter.Column("UnitPrice").OnTable("Foo").InSchema("dbo")
                .AsDecimal(28, 10)
                .NotNullable().WithDefaultValue(0);
            Alter.Column("UnitPrice").OnTable("Foo").InSchema("dbo")
                .AsDecimal(28, 10)
                .NotNullable().WithDefaultValue(0);

            this.EnableSystemVersioning("dbo", "Foo", "dbo", "FooHistory");

[jzabroski]

@smitpatel One more helpful tip based on what I've encountered adding my own temporal table support for EF6 projects. The following documentation basically implies that EFCore cannot support temporal tables on SQL Server 2016, because EFCore (the last time I checked) uses cascade delete to manage referential integrity.

See: https://docs.microsoft.com/en-us/sql/relational-databases/tables/temporal-table-considerations-and-limitations?view=sql-server-2017

ON DELETE CASCADE and ON UPDATE CASCADE are not permitted on the current table. In other words, when temporal table is referencing table in the foreign key relationship (corresponding to parent_object_id in sys.foreign_keys) CASCADE options are not allowed. To work around this limitation, use application logic or after triggers to maintain consistency on delete in primary key table (corresponding to referenced_object_id in sys.foreign_keys). If primary key table is temporal and referencing table is non-temporal, there's no such limitation.

Note

This limitation applies to SQL Server 2016 only. CASCADE options are supported in SQL Database and SQL Server 2017 starting from CTP 2.0.

Our build server is still using SQL Server 2016, so this was an unexpected hiccup when I went to turn on CI for a new project.

[roji]

@jzabroski EF Core doesn't require cascade delete - there are other options for managing referential integrity, e.g. having EF Core itself issue cascading deletes. See DeleteBehavior and the cascade delete docs (albeit a bit out of date).

[jzabroski]

@roji Unless I am mistaken, I think we are talking past each other. I must confess, I am using EF Core 3.1 in my current projects, and my understanding is that if the child entities are not loaded and I delete the parent entity, EF Core will not delete all associated child entities for me if I delete the domain root. For this use case, ON CASCADE DELETE is required. As you can see from the DeleteBehavior document you linked to, it says (emphasis mine):

Enum Name	Enum Value	Behavior Description
ClientCascade	4	For entities being tracked by the DbContext, dependent entities will deleted when the related principal is deleted. If the database has been created from the model using Entity Framework Migrations or the EnsureCreated() method, then the behavior in the database is to generate an error if a foreign key constraint is violated.

To contrast this behavior with NHibernate, where you can configure the delete behavior to be something like:

BEGIN TRANSACTION
  DELETE FROM dbo.Child WHERE ParentId = @ParentId
  DELETE FROM dbo.Parent WHERE ParentId = @ParentId
COMMIT

EFCore does not support this. Anyone I know who is building real world applications with a decent amount of child record rows will want THIS behavior and not the ClientCascade behavior. The ONLY alternative that works in practice is ON CASCADE DELETE. Everything else is just theory or only viable in very small demo applications, and not at all viable for any kind of Big Data app.

[roji]

@jzabroski you're right that EF Core doesn't currently support bulk deletes of dependents to cascade deletes - that's tracked by #18960. But it's not accurate to say that ON DELETE CASCADE is required by EF Core - many avoid it because of the SQL Server limitation on circular cascading deletes, for example.

In other words, I'm not sure this actually blocks temporal table support.

[jzabroski]

@roji It may not block it but it is a very good thing to call out in the documentation. I tend to find with ORMs that these types of configuration limitations are definitely under-specified, so helping to call them out to @smitpatel likely helps him build a better product and avoid people opening support requests for things that he can't do anything about.

[Ogglas]

Really long wait for this. I hope this will be prioritized since it is now at the top based on label consider-for-next-release and sort on reaction 👍

[maumar]

Initial support for Temporal Tables has been added here: e7c0b9d (model/metadata part) and here: 4b25a88 (query part) and will be available in the next preview (Preview 8), as well as in current nightly bits.

Usage:

Mapping entity to a temporal table can be done in the OnModelCreating, like this:

modelBuilder.Entity<MyTemporalEntity>().ToTable(tb => tb.IsTemporal());

additional configuration is also supported - history table name/schema, names for period start and period end columns

modelBuilder.Entity<MyTemporalEntity>().ToTable(tb => tb.IsTemporal(ttb =>
{
    ttb.HasPeriodStart("SystemTimeStart");
    ttb.HasPeriodEnd("SystemTimeEnd");
    ttb.WithHistoryTable("MyHistoryTable", "mySchema");
}));

Migrations are supported so existing entities can be converted to temporal.

Querying:

var myDate = new DateTime(2020, 1, 1);
context.MyTemporalEntities.TemporalAsOf(myDate).Where(e => e.Id < 10);

Supported operations: TemporalAsOf, TemporalAll, TemporalBetween, TemporalFromTo, TemporalContainedIn.

Some limitations and considerations

• Queries that use temporal operations are always marked as NoTracking. Multiple entities with the same key could be returned from such queries and EF would not be able to resolve their identities properly otherwise.

• Temporal operations are supported directly on DbSet, rather than IQueryable. In case of inheritance, they can't be applied on OfType operation. Instead, use:

context.Set<MyDerivedEntity>().TemporalAsOf(...); 

• Navigation expansion is only supported for AsOf operation, since it's the only temporal operation that guarantees consistency of the result graph. For other temporal operations navigations must be created manually using Join.

• When expanding navigation, the target entity must also be mapped to temporal table. Temporal operation gets propagated from source to the target. Navigating from temporal to non-temporal entity is not supported.

context.Customers.TemporalAsOf(new DateTime(2020, 1, 1)).Select(c => c.Orders)

will return customers and their orders as of Jan 1st 2020. Temporal operation gets applied to customers AND orders automatically.

• Set operations (e.g. Concat, Except) on arguments mapped to temporal tables are not supported. (issue tracked here Temporal Tables : add support for set operations #25365)

Note:
This thread is already very long, for any feedback, questions, bug reports etc. please go here as a starting point: dotnet/EntityFramework.Docs#3353